Returning a Value from a Data View

Epicor ERP 10
,
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?

21

Not exactly what I was saying. But yes, you can use linq or even SQL directly on UD11 to get max.

I was just explaining my setup which is 1 row per company containing all incrementing numbers in Epicor.

Research linq queries and you should find plenty of examples.

Sorry for the confusion.

22

Dan:

I guess I wanted to understand (and certainly learn something new) the strategy behind maintaining incremental PKID information in a separate UD table (in a number field) as opposed to just pulling the PKID from where it might sit in its existing UD table key field. I wasn’t sure if this might be solving something that has come up for developers that I haven’t experienced just yet.

I’ll try using the LINQ method a see how that goes for me.

Thanks for the suggestion, Dan. I’ll let you know how things progress.

23

Yup, this works great.

image
image1176×546 154 KB

1 Like
24

What’s a PKID and why do you need it? You should easily be able to grab the next value. You could use a dynamic query and call your SQL that you wrote, or in a BPM do a linq query like @duckor suggested. Depends on if you want your logic client side or server side.

25

A few things,
If you are just trying to have an incremental Key take a look at the existing Epicor Solution to accomplish this here’s a walk through

Further more, if you want to maintain your own (for some reason) then the approach should be a BPM and not UI. Simple put a Post Processing BPM on GetaNewUD11 that does what your SQL query does that is find the Max of whatever and then assign that to your Key Field.

3 Likes
26

Thanks, Brandon. I’ll make sure to have this tool for animation demonstrations that I might need to offer in the future. A appreciate the tip.

27

Brandon:

PKID : Primary key identifier.

I’m trying to simulate a primary key for the UD11 table so that it can be used for lookups when I build a “tracker” UI - or for any other table relationship requirement that might need to be established between UD11 and other Epicor tables that also contain UD fields.

28

I guess I’m not understanding what you are trying to accomplish. The UD tables all have 5 keys that make the record unique. These keys once established, can’t be changed, the row has to be deleted. You can make these keys anything you want (as long as they are strings), so you can use job number, asm seqences, partnumbers, any combinations. The combination just has to be unique. You shouldn’t need to use another table to do this.

If you just need an incremental next key, Jose’s example with a BPM is a way to do that. But your strategy depends on your overall project and what you are trying to do with the data.

29

José:

After spending 2-and-a-half days trying to use other methods, I came in this morning and needed roughly 15 minutes to implement your suggestion:

image

Thanks for this information, José. I’ll make sure to employ this method, should I need to do so in the future.

Only thing that irks me is not understanding why I couldn’t return data from the other methods I had attempted earlier, especially when I could return correct record counts of existing records in the UD11 table. If I can return a count of records, then why not the values of those records? Seems odd to me.

Anyway, I appreciate the input. You definitely got me situated firmly.

30

The data view is only what you have loaded. So if you don’t search and load all of UD11, you won’t have all of the data to deal with.

Try this, do a search and pick like 5 records. Then try your buttons with the code. The count one specifically. If will give you a count of how many records you loaded into UD11 entry.

1 Like
31

As @Banderson correctly states, a dataView (as its name implies) is a view of a piece of data, in this case it is the current data that is loaded on the screen.
So for example

EpiDataView ud11 = oTrans.Factory("UD11");
MessageBox.Show((string)ud11.dataView[ud11.Row]["Key1"]);

Will display the current Key1 field of the currently selected record on the screen. You can’t use a data view to get information on other un-related records. To do that you need to run a query of some sort there are many ways to skin that cat

  • BAQ via Customization
  • BO Reader
  • BO GetRows Method
  • Adapter Invoke Search Method
  • FormSearch Library Search Method

among others

1 Like
32

Brandon:

Yes. I do understand the availability of Key1 through Key5, but if I want UD11 to act as any table would with a unique identifier, I’d still need to create a unique value for one of those key fields, no? That’s basically what I was trying to do - use, in this case, “Key1” as the PKID and assign a unique value to it based on the highest/latest/most recent value in that same “Key1” field. That way, a new record is uniquely identified from the others in the UD11 table.

33

Yeah, that’s one way to do it. But if you are relating things to other stuff that’s already unique, you can just use that stuff for the keys. Like I said, it really depends on the project. I’ve used a lot of UD tables, and have not needed to use a “next number” technique. I have used GUID to get a unique key. Depends on how badly you need to sort. But sorting by a key is probably not that useful. A date is usually better. But (broken record) it depends on the project.

34

Brandon:

Thanks for hanging in there with me through this thread. I really appreciate your input, as always.

As I’m thinking about it, I might have (during my previous attempts above) incorrectly assumed that I could fill a DataTable object and retrieve the value directly from the DataTable (my knowledge of C# isn’t on an expert level at this point). Of course, with the method José provided, this is all moot - however, I still want to understand where I previously took a wrong turn.

If I can get record counts in the following manner (ignore the “DBConnector” method because that merely does the work of connecting to the database and returns a DataTable according to the passed query):

image
image.png819×477 29.1 KB

… then what is the correct way to reference and return actual field values, such as “Key2” in this case, from the DataTable object? I wasn’t able to return a value, which means I must not have been referencing the column/field correctly.

Again, this is merely so I can understand my error.

EDIT : It makes sense that a data view would not have returned anything because I didn’t have anything on the form, at that specific time, to reference in the view. However, a simple call to the database using a SQL connector and filling a DataTable object should have worked.

35

I guess I’ve never used that connector. If I needed data from the DB on the client side, I used the DynamicQueryAdapter, and just ran a BAQ. When you do that, a “Results” table is returned and you can get the values from that.

1 Like
36

Ok - Thanks again, Brandon. I always enjoy your help and suggestions.

1 Like