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.
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.
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.
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.
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.
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.
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.
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:
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.
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.
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
among others
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.
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.
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):
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.
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.
Ok - Thanks again, Brandon. I always enjoy your help and suggestions.
