I’m part of a company that’s currently in-process developing our IFS solution, and as the data admin I’ve run into a particular problem that I’m hoping someone might be able to advise me on. As part of workshops we’ve been told by the IFS consultants that we should be using the logical unit IDs as a functional part of the workflow processes. For example, a part number could be used as a reference across multiple parts of the business, having to do with inventory, shipping, R&D, etc…
As the data admin, I’m a little concerned about this. I’d like to list a few things I’ve noticed during this process that led me to my concern. Please correct me if I’m wrong, because these are only my current understandings, and I could be mistaken!
1 - The IDs for at least some objects can’t be (or shouldn’t be) defaulted. Using Parts as an example, a user would be asked to enter the part number they wish to use when creating the master part record. There’s no way to inject a default or system-generated ID if that particular part of the system doesn’t already use a default. Is my understanding of this correct?
2 - Because a BeforeInsert trigger won’t allow you to modify the record fields, I can’t correct the ID before it’s added to the table. I could perhaps run validation and simply refuse the record, but other than throwing errors I can’t assist the user. Again, is my understanding of this correct?
3 - Even though IFS has object identities it uses behind the scenes to ensure record uniqueness, the ID the user is entering is being treated as a proper ID. That means I can’t change the ID once it’s been inserted, except for removing the record.
With those three points, I’m cautious about using the ID as a functional field. If I can’t default it, validate it, or fix it after manual user entry, I just can’t rely on it being right, and if I don’t plan for user error, I know I’ll end up regretting it further down the line.
Please let me know if I’m thinking about this correctly. I’d like to trust the IFS consultants’ advice here, but I see red flags and I want to be sure.
-J Fox
Page 1 / 1
1- Yes, you are correct, some IDs can’t or shouldn’t be defaulted. Parts is a good example as usually there is some other schema driving this ID sequence. Either an engineering planned format, following a supplier sequence or some other pre-established method of determining the user viewed id (ie, part number)
2- Yes, the create new version of the user record is hard to modify using a trigger, you will generally have problems trying to control this unless you do it from a custom view or some other custom method which often negates the usefulness of the form being utilized. Depends on what you are trying to control or validate though.
3- Yes, once the ID is saved (Customer ID, Customer Order Number, Purchase Order Number, Part Number, Voucher Number, etc) it cannot be changed. In fact, on any given view, there can be more than just the ID that becomes set once the initial record is saved. I refer to them as one way doors, once crossed, there is no return. To control, there is only then Delete or in some cases, Cancel and the offending record can’t ever be removed. Again, which record and the behavior depends on how persistent the record is. (Customer Orders can’t be deleted once saved but Shop Orders can be deleted if not progressed beyond a certain point)
You will definitely want to use the ID as a functional field, if you don’t, you will forever be fighting against attempting to control it, but you will generally lose - not to mention, you can’t control the interconnectivity inherent in the system for the ID fields. In the end, you have to set some boundaries and build the control process around using the field as a necessary part of the process. If you aren’t going to, you might as well just build your own ERP system if you want to control it that tightly.
You can control the identity sequence for the default or next increment for certain values.
OR
These are a couple classic examples, you would work to build your process around these values and a few others.
Example of Customer Order - ignore Customer Name, but the other 6 fields once saved on a Customer Order cannot be updated or changed. Enter the wrong site, cancel the order. Enter the wrong currency, cancel the order. Those are the only corrections available. You just have to learn to live with it.
My advice - as much as it goes against your nature and intention of keeping the database clean and organized - move on from trying to perfect the use of the logical unit IDs and just plan on setting some rules for them that fit your organization.
1- Yes, you are correct, some IDs can’t or shouldn’t be defaulted. Parts is a good example as usually there is some other schema driving this ID sequence. Either an engineering planned format, following a supplier sequence or some other pre-established method of determining the user viewed id (ie, part number)
2- Yes, the create new version of the user record is hard to modify using a trigger, you will generally have problems trying to control this unless you do it from a custom view or some other custom method which often negates the usefulness of the form being utilized. Depends on what you are trying to control or validate though.
3- Yes, once the ID is saved (Customer ID, Customer Order Number, Purchase Order Number, Part Number, Voucher Number, etc) it cannot be changed. In fact, on any given view, there can be more than just the ID that becomes set once the initial record is saved. I refer to them as one way doors, once crossed, there is no return. To control, there is only then Delete or in some cases, Cancel and the offending record can’t ever be removed. Again, which record and the behavior depends on how persistent the record is. (Customer Orders can’t be deleted once saved but Shop Orders can be deleted if not progressed beyond a certain point)
/...]
My advice - as much as it goes against your nature and intention of keeping the database clean and organized - move on from trying to perfect the use of the logical unit IDs and just plan on setting some rules for them that fit your organization.
Thanks for the advice. Heard and understood. You’re right, it does go against my nature to allow manual user input to work as an immutable functional field. I’ll work with my team to figure out what the best way for us is going to be!
