I am trying to figure out a way to populate a subtable based on two different linked fields. Sometimes I want to select field A1 to load all the fields from sheet 1. Sometimes I want to select field A2 to load all the same fields, but from sheet 2 (and sheet 2 is actually the sheet created by the same subtable). Is this possible? Or is there a workaround?
I’m guessing my explanation above is hard to follow. Some information that might help to understand my design:
• The subtable is actually invoice sales line items.
• Each record is a different invoice and has its own subtable.
• Every line item in all subtables is assigned a unique, auto-generated number that cross references to a linked sheet generated from the subtables (create sheet from subtable).
• Sometimes I want to link and load a sales line item from a sales catalog sheet (sheet 1)
• Sometimes I want to link and load a sales line item from a previous invoice (sheet 2, which is actually the sheet created by the subtable). Basically, I want to copy a previous sales item from an earlier invoice, modify it, and then have it save to the linked sheet as a new entry.
Not sure if I understand it correctly.
If both sheet1 and sheet2 are fundamentally different version of the same table, it’s possible to load the same field using a shared “key”.
However, since that in different scenario, you would want to load different group of line items.
I think you can achieve this by using a field to Dynamically filter which group of line item you want to Link and Load by selecting the shared “key”. Instead of having either sheet 1 or sheet 2 which might show only limited entries (maybe by Fixed Filter), you can create another sheet 3 that allow user to select from entries from both sheet 1 and sheet 2.
I think the key is to use the same form base for both sheet 1 and sheet 2, but use category to distinguish them into different version. This way you will be able to select them based on which type of entry you need.
In your case, you can actually have 3 keys on the shared form. Since that you might have respective “parent form” for both “sales catalog sheet” and “previous invoice”.
E.g.
Key 1: LineItemNo: shared by both types
Key 2: SalesCatNo: used by Sales Catalog Sheet
Key 3: InvoiceNo: used by Previous Invoice
[Sales Catalog] you can have this as subtable through Key 2: SalesCatNo
[Invoice] you can have this as subtable through Key 3: InvoiceNo
In the shared [LineItem] table: you can use Key 1 to find both type of entry.
However, you mentioned that you want to
It seems like you want to select old invoice line items and make them into new entries, you will need to have different sets of field I think.
Too bad. Actually, there is no technical reason why this couldn’t be implemented by Ragic. Every time a new parent link is selected, the appropriate fields are loaded. This would only be a problem if the links were synced.
The shared sheet with 3 key field can work as subtable “line items” for sheet and sheet.
My suggestion was that the 3 sheets are actual different versions of the same sheet using form function: Multiple Versions
The backend database is the same, just displaying different set of fields or records based on different interface design and data filters. So there is no need for syncing. Search & Filter
However, if currently your sheeet 1 and sheet 2 are separted, you need to start by combining them into the same sheet first, and also give them a Type field and additional Key fields to distinguish them.
The tricky bit is when you select an existing record as the template for creating new record. You will need to reference the table itself. And you might need to copy and fill the new record based on the fields from the eixsitng record selected. Need to be careful about the part to get it right.
