So you have various directives and rather than hard code parameters you store them in the database
but where?
what works best?
Add UD columns to the company table for example?
Use a whole UD table for the purpose?
Or something more ‘exotic’ ?
UD fields on the company table have served me well for this. Allows you to store things like API keys there instead of hard coding
At my previous company we used a lot of UD fields on order and job tables. At my current company we’re on cloud, so creating a UD field requires putting in a support ticket to regen the data model. I’ve been leaning more toward using UDxx tables whenever it makes sense. For example, I have one UD table full of lookups for product configurators. Key1 = configurator ID, Key2 = row type (e.g., “FrameExtrusion”), Key3 = lookup key (e.g., a color name), and Character01 = PartNum. I used a similar technique at my previous company to create a many-to-one lookup between customers on our various ecommerce sites and customers in Epicor. Key1 = site ID, Key2 = site platform (“Magento”, “Shopify”, etc.), Key3 = external customer ID, Number01 = Epicor CustNum. To replace UD fields with a UD table, you could have Key1 identify the relates-to table, Key2 identify a row in that table, and the value columns could represent what would otherwise be numerous UD fields. The issue is keeping track of which columns are being used for what. Still, on cloud I’d use this approach without hesitation. If nothing else, use it in development. Once you’ve identified all the UD fields you’ll need, create them all at once so you don’t have to keep waiting for a regen.
I always suggest using the User Codes table… it is exactly what this was defined for. You can create your own usercode “table” (actually, a list of values) and then define the values in that table. The table can be linked via BAQs, and can be used in BPMs to find the value for various needs. I have used the user code table to hold the “Next” value as well (although there are better ways to do this now).
One thing to be aware of about User Codes is that they do not play nicely with paste-inserting into EpiGrids unless the keys are identical to the values. Users must paste-insert tabular data containing the keys that they normally never see, not the values they’re familiar with.
DMT can be used to upload User Codes… but for parameters (as the OP asked about) this works really well…
I’ve thought about using usercodes for creating KV pairs, but the key can only be 10 characters and the value can only be a string type.
I had a thought of replicating the way parameters are stored for scheduled tasks in Epicor - see table ice.systaskparam
I don’t mean to create the codes, but to use them. Say you have a UD field on OrderDtl that’s supposed to store the key of a User Code. You can easily add an EpiCombo to the UI that displays the values and stores the keys. This works fine. But if you add that UD column to an EpiGrid so you can include it when paste-inserting lines on an order, you have to paste the keys, not the values. Perhaps the real issue is EpiGrids not playing nicely with EpiCombos where they keys and values differ.
you can customize the usercode table to add your own UD Fields… I have also used the character field to store a numeric value… it just takes a conversion to use the value, but that is not a big deal. There is a KEY, a value, and a long value to use. It is not as efficient, but you can also use a key that is generic, and then use the value to lookup your parameter, and store the actual value of your parameter in the longer description.
