Enumlist Base Type: Ref --> Add New Value --> form...

ether_wendy · 12-07-2021 07:54 AM

Hi All

I have a parent table “Work Orders”, and a child table “Equipments,” where Equipment is part of the " Work Orders" table. I want the users to be able to select which equipments they are working on for the work order, or be able to add a new equipment to the work order. I create an Enumlist column in work order table with base type Ref and select equipment table as referenced table. The enumlist works fine. However, when a user tries to add an equipment in this work order by clicking “add” under enumlist dropdown, and it goes to the “Equipment_Form,” the column “Associated Work Order” cannot auto-populate (not able to dereference?).

I know only “REF_ROWS” formula can do the trick to have auto-populated column. I am just wondering if there is any other way to work around it? Or if there is any better way to achieve this?

Thanks !!!

Marc_Dillon

There is no way to auto-populate when going through the “Add” selection in an EnumList’s dropdown.

But seeing your situation, I’m wondering how the “Associated Work Order” column in your Equipment Table is being updated in any case, whether adding a new record or not? Can the user just add an Equipment record through the dropdown “add”, and then select it and proceed the same that they would if just selecting an existing record?

I also imagine your data structure is probably not optimally built here. I’d suggest a third Table, “work_order_equipment”, that has 2 Ref columns, one to Work Order, and one to Equipment. This is a child Table to a Work Order.

Do some research on “many to many” relationships for more info.

https://community.appsheet.com/search?q=many%20to%20many

ether_wendy

Hi, Thanks for your reply! I actually follow the method by this many to many relationship article (Many-to-Many Relationships). This allows the users to select an equipment in the Work Order form (the enumlist base type ref) and allows them to add a new equipment if needed. I also put a formula in “Initial Value” in enumlist column:

UNIQUE([_THIS] + LIST(MAXROW(“Equipment”, “_ROWNUMBER”, [Associated Work Orders] = [Work Orders Key])))

so the newly added equipment will automatically be selected in this work order.

On another note, regarding adding a third table, are you referring to the method mentioned here (Help with many-to-many reference and bridge table) about having a bridge table?

It would be great if you could elaborate more on how this third table will work, as we actually have a lot of tables that IsPartOf or referencing Work Orders or each other at the same time (eg. Clients, Receipts, Materials…). Thus, I am wondering if a third table is needed for every reference relationship…?

Thanks so much!!

Marc_Dillon

Yes, it is commonly called a “bridge table”. I’m not 100% if it is appropriate for your app though. Is an Equipment record a unique physical object that can only be part of a single Work Order?

I’m really not sure what else to elaborate on about “bridge” or “join” tables. But maybe you can do a google search about those terms, or “data normalization”, to gain more insight?

There really isn’t an always-correct way about it. It all depends on the functionality you expect, your exact implementation, and the desired user interface. Sorry I can’t say much more here , the “database normalization” subject is pretty in-depth.

Hmm. I’m not really sure what the point of this expression is. I’m not seeing how it would be helping you in any way.

ether_wendy

Thanks so much for your time and explanation! Will look into it.

Enumlist Base Type: Ref --> Add New Value --> form with prepopulated dereference column?