Returning a Value from a Data View

Epicor ERP 10
,
1

After researching the Google-verse on this topic and reading about 20 to 30 non-conforming, non-agreeing, completely different examples (and certainly trying a few of them in Epicor’s custom script editor), I seem to be more confused about this than I was when I previously started.

Let’s say I have only two records in “UD11”.

My requirement is to sort the data view by “ShortChar01” in descending order and return only a specific value within the first row of data in that view. I’ve read where the data view does not accept any parameters related to “row” or “rows” - and that the view would need to be converted to a data table first (of course, no examples of how this is accomplished and further research on that generated even more confusion for me). Anyway, here’s what seemed to make sense for me to do (see below), but the “strPartNum” value contains no data.

image
image.png769×185 6.32 KB

I create a new data view, then define it as a view for “UD11”, then sort the view by the “ShortChar01” field in descending order.

Now, my understanding is, in order to return values from a specific row, I’d have to pass the row index on the data view. Since I’m not doing that, any row reference will be applied on the first, or current row in the data view. Since I’ve sorted the view prior to retrieving data on the first/current row, I should be getting a value from the “top 1 of all [ShortChar01] fields”. My expectation here is that I should be returning a value of “100-570-1010” from the sorted rows on the “ShortChar01” column. Yet, nothing gets populated in the “strPartNum” container.

image

Since nothing is being returned, I’m obviously missing something, or using the wrong method for this. Might someone point me to a reference that might provide me with a better understanding of the EpiDataView object, how to properly define it, navigate through it, and how to return values from it? Also, is there a Epicor best-practice for referencing the EpiDataView (I ask merely because it seems to be a wild frontier out there with the numerous differing examples)?

Thank you for assisting.

2

In debugging… what is the value of edvUD11.Row ? Sorting your dataview may not necessarely select a row. I would assume edvUD11.Row is -1 as none was selected.

What is the result if you you directly put 0 as
edvUD11.dataView[0][“ShortChar01”] ?

Pierre

3

Hello, Pierre. Here are the results:

image

4

Are you sure that you do not have an empty row in the DataView?

What is the result for row = 1?
edvUD11.dataView[1][“ShortChar01”] ?

5

Well…bizarre…

That should have worked then…

Ok maybe overkill… but try the following after your sort…

foreach(DataRow row in edvUD11.dataView.Table.Rows)
{
	MessageBox.Show(  row["ShortChar01"].ToString());
	
}

just to verify

6

Mehis:

The UD11 table currently has 3 rows of data. When I use the example you have there to return a row index of “1”, nothing is returned.

7

Pierre:

The UD11 table currently has 3 rows of data. When I insert the loop example you have provided, it runs through the loop only once and returns nothing in the message box. I would have expected it to loop through and show me 3 separate message boxes because there are 3 records in that table, but the message box appeared only once.

8

Try this simple one - to get a value from the Client context:

EpiDataView edv = (EpiDataView)this.oTrans.EpiDataViews["CallContextClientData"];
string userID = edv.dataView[0]["CurrentUserId"].ToString();

Although the only difference I can see between this and your code is that I declare are set the edv variable in 1 statement, and have no brackets. You’ve got an extra set of brackets?

9

Thanks, Mark. Yes - that code block example returns my Epicor user ID (I can return the “userID” string in a message box).

However, taking it further in an attempt to reference anything out of the “UD11” table, doesn’t seem to work for me:

EpiDataView edv = (EpiDataView)this.oTrans.EpiDataViews["UD11"];
string strChar02 = edv.dataView[0]["ShortChar01"].ToString();
MessageBox.Show("Result = " + strChar02);

(I’m sure I don’t have to convert a string to a string there, but I left it in place. In addition, I tried to reference the “Key2” field, which is an Int32, and that didn’t work either.)

Might there be some other reference I need in order to work directly with UD tables?

10 
EpiDataView [edvVarName] = (EpiDataView)(oTrans.EpiDataViews["UD111"]);
string [VarName] = [edvVarName].dataView[[edvVarName].Row]["ShortChar01"].ToString();

So that’s from the object explorer

if we modify it as we need.

EpiDataView edv= (EpiDataView)(oTrans.EpiDataViews["UD11"]);
string strChar02  = edv.dataView[edv.Row]["ShortChar01"].ToString();
MessageBox.Show(strChar02 );

That should get you your current row. Does that work to get something?

image

Also, I’m assuming that you are doing all of this in the UD11 entry screen right? (Just had to ask to make sure)

11

Brandon, I was thinking the same thing. It was never mentioned what form was being used.

what about
EpiDataView edv = (EpiDataView)(oTrans.EpiDataViews[“UD11”]);
MessageBox.Show(edv.dataView.Count.ToString());

1 Like
12

Although if it wasn’t there, it would return an error. I was testing in UD14 and forgot to change it, and it sent back an error. So the view must be there if it’s not throwing an exception.

1 Like
13

That works here.

image

		EpiDataView edv= (EpiDataView)(oTrans.EpiDataViews["UD14"]);
		string strChar02  = edv.dataView[edv.Row]["ShortChar01"].ToString();
		MessageBox.Show(strChar02.ToString() +" " + edv.dataView.Count.ToString());

his is the exact code from the button.

14

Brandon - that also returns nothing (I’m getting a blank message box). Yes - this custom code is being written in “Ice.UI.App.UD11Entry.UD11Form”.

15

And just to be sure, you are loading data into the form prior to running the script right?

16

Just for funzies, can you make a gif of the whole process? From screen load to button click?

17

Dan:

No… no data is loaded on the form at this point in the code and that why I was wondering initially…

From the originating post:

“Since nothing is being returned, I’m obviously missing something, or using the wrong method for this.”

My guess is that I’m totally using the wrong approach by thinking an EpiDataView is the correct tool to use. What I’m trying to do, specifically, is… as a new record is requested from the top menu, I want to look at the existing records in the UD11 table (this might get slightly confusing because I’m going to refer to fields that I didn’t use in my examples here in this post) and isolate the max value of “Key1”. Once that has been isolated, increment it by 1 and use that new value as the “Key1” value for the new UD11 record that was requested.

Basically, this is a simple effort to try and mimic creating a PKID for the UD11 table.

My line of thought was to do this during the “EpiViewNotification” event, when the notify type is “AddRow” (when a user requests a new record).

So, to reiterate, at this point, no data exists on the UD11 form. It’s a workflow of basically requesting a new record and at that point, kicking off custom code to look at what is currently existing in UD11, then grabbing the max value of “Key1”, then incrementing it and putting that new value into the “Key1” field for the data view of the open/new record.

Frustrated with the data view approach, I also tried to construct a SQL statement:

string strSQLQuery = "SELECT MAX(Ice.UD11.Key2)"
                           + " FROM"
                           + " Ice.UD11";

… and toss that into a data table object. Without the MAX statement, it’ll return the count of 3 rows that currently exist in the UD11 table. With the MAX statement, it’ll return a count of 1 row. All of that is expected - BUT - it will also behave exactly like the data view approach and return no values whatsoever.

I have also tried something like:

var varMaxNMR = (from row in Db.UD11 select row).LastOrDefault();

… but the compiler didn’t like the “Db” schema (nor did it like the “Ice” schema) and I didn’t know how to properly reference it. “LastOrDefault” was used here because what I need is the latest row in order to get the latest “Key1” value. In this manner, I didn’t exactly know how to replicate the MAX statement.

So, seems like I’m using the wrong tool for the job, however, I’ve tried two other methods and I’m not successfully piecing those together nicely either.

What might be your suggestion(s) here?

18

Brandon - I don’t think I have a tool for creating animated GIFs on-the-fly like that. Might there be a specific bit of software you’d recommend?

19

Hey Bill, that’s what I thought you might be doing. The tool you would probably want is a linq query to return max value of key1. The way I handled this was to dedicate a UD table with 1 row per company(we have 5) and store the values into number01-number… for any custom incrementing value like this. In this case I call the ud adapter, connect and adapter.getbyid (companyID, “”,””,””,””,””), return the value I need and add one, update and dispose. Maybe a lot of overhead but it’s all contained in one place. Also a gif recording program I use is recordit.

Edit: FYI data views are to access the loaded form data. So anything currently on your screen.

20

Thanks, Dan.

I think I’m picking up what you’re putting down here. So, you’re saying in order to sort of “keep track” of the state of PKIDs for several UD table solutions (not just the UD11 one I’m working on), I can leverage, say “UD01” and perhaps assign “UD01.Key1” as the field responsible for maintaining the PKID for my UD11 solution. If I need a new PKID for UD11, I would be building a LINQ query back to “UD01.Key1” to grab the current value.

This’ll likely be a dumb question, but - if I can utilize a LINQ query to “UD01.Key1” in order to get the current PKID maintained there, why wouldn’t I simply do that for “UD11.Key1” which already exists there?