Replace a multiple step IF statement with separate table

[blaineiler]

I am looking for a way to make a data field on a table dynamic based on another selection. We manufacture goods with batch codes. We have our own internal batch code format based on production line and date. However, we have some products that need to have a batch code with a slightly different format. It might have different data and/or setup that data in a different way than we do for our standard batch code.

For example, our standard batch code format is XXYYDDD where XX is the production line (e.g. L4), YY is the last two digits of the year (e.g. 24 for 2024) and DDD is the julian date (e.g. 173 for June 21, the 173rd day of the year).

However, we may have some products that get a batch code of ABC YYYYMMDD where ABC is static text, and YYYYMMDD is the date of production.

We have a need for multiple batch code formats across the various products we make. If I setup a formula for the batch code field on my work orders table with if statements, it would be a lot to keep up with. Instead, I was envisioning having a table where I could create and maintain "batch code formats." I would then assign that batch code format to the item record. Every time a work order gets created with that item, it looks up to the batch code format to determine what the batch code should be.

While I think this setup makes sense, I can't figure out how I would setup the actual data in the batch code format table such that it could look up to variable data on the work order record (e.g. date, production line, etc.). Any ideas would be much appreciated!

[Suvrutt_Gurjar]

---

@blaineiler wrote:

would then assign that batch code format to the item record. Every time a work order gets created with that item, it looks up to the batch code format to determine what the batch code should be.

---

In general, it sounds that you are looking for referencing between table.

Add references between tables - AppSheet Help

So possibly batch formats are referenced in the items table?

[blaineiler]

It is a bit more complicated than that. Yes, there would be a reference from the product table over to the batch code format table. However, in the batch code format table, It would have columns like ID, Format Name, and Format. The Format field would be the template that could reference data fields in the Work Order table like [Date] and [Production Line]. I'm assuming the Format field would be a text field, but struggling with what the data should be to reference fields in the Work Order table.

[Suvrutt_Gurjar]

Thank you for the additional details. I think more details may be required for more relevant suggestion. But in general, it sounds that an IF() based evaluation will be necessary based on the batch code format selected for an item. However  as long as format is made of a finite fields such as date and a few more, possibly an efficient IFS() or SWITCH() could be tried.