Hi guys probably an easy answer here but is there a sequence ID for all sales order lines in the system?
Similar to the DemandSeq field in PartDtl table. Problem is that is only for open lines, I need to include closed lines as well.
Any help is appreciated!
Short Answer… no each order starts at 1 and works its way up
so there is not a unique ID for each order line in the system?
can i CONCAT the order number and line to create a unique ID?
sorry im not a programmer outside of epicors UI
Well, yes, the order number plus the line number is what makes each order detail unique. Same goes for the releases of the detail… The OrderRel table requires a three field index to get each unique record… Order, Line, and Release… Sometimes this is represented as something like: 12345-6-7 which would be order number 12345, line 6, release 7.
Separately, every record in Epicor has a unique ID: SysRowID. It’s a large, meaningless globally unique ID (or Guid). They can be useful at times but there is no information one can glean from them. Just an FYI…
Ok… yes, Mark is very right… but the GUID is unique to that instance on that computer… if you had the same order/line/release on a different computer, it would be a different guid. a sample guid looks like this:
These seemingly random values are just that… they are generated on the fly when needed, and then used by the system internally. you can even generate some unique values for yourself and read about them here: Free Online GUID Generator
Mark that is exactly what I was looking for. Knew it existed, couldn’t remember the field!
And to further confuse things … the components of a Sales Kit actually create OrderDtl records. So if you’re looking for just the “lines” that would appear on the order, you need to check the KitFlag and KitParentLine fields, in order to exclude the kit components.
Just curious … what is the
DemandSeq field you’re referring to in PartDtl? I see a couple of fields with “Seq” in the name. But none are unique - they are used with other fields too.
all I needed was to pull the most recent order info. Sort of like in the Part Advisor screen
So I used max (sysrowID) in the baq. Verified it always pulls the most recent record, and bob’s your uncle!
I could be wrong, but I don’t think the SysRowID is guaranteed to be sequential (not meaning every value, but rather ever value is greater than any previous value)
SysRevID should be, as it is a timestamp.
sorry Calvin it’s actually PartDtlSeq
its just the sequence ID for each line of supply/demand in the system
very helpful in BAQs
wait does that mean the rowID will change if someone updates a line?
SysRowID is set when the record is created. It is a GUID and should never change. The SysRevID is updated every time the record is changed.
The main priority of a GUID is its uniqueness. Creating them in some sort of order, diminishes the scope of available unique values. If the GUID algorithm uses a datetime stamp as part of the value, then it may appear to have the property of always increasing.
it looks like some GUIDs are created by combining the MAC address with a date time stamp. If the App server is what is generating the GUID, then the MAC address would always be the same. That is until the app server is moved to new hardware, virtualizations, etc…
So the SysRowID may very well appear to be ordered, but that’s not guaranteed.
Minor clarification (because it bit me in the behind in the past) while the dataset type is timestamp (now deprecated to “rowversion”) it is a misnomer in MSSQL it has no relationship to date or time it is just an incrementing 8 byte number
The rowversion data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime2 data type.
Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a rowversion column within the database. This counter is the database rowversion. This tracks a relative time within a database, not an actual time that can be associated with a clock.
youre right its not perfect order as I’m finding out now… thanks for the help Calvin!