There will be bunch of workaround to achieve the same goal and task.
For instance, you will use PIC 2 table as sort of “calculator” to dynamically calculate the total, based on the user selections. To do that, follow those steps.
- PIC 2 table, delete Date field as it believes it will not be need, unless you have reason to keep that field.
- Instead, name that field as [ID] On the spread sheet, add the value to this ID fields randum text.
- Read PIC2 table and set “update” only.
- for [Brand] Field, add formula Unique(Select(PIC1[Brand], true) ) as suggested value. This will dynamically generate the dropdown based on the value of PIC1 [Brand] without creating master table.
- Do the same for [Lot No.] field, i.e. place the formula to suggested value with formula Unique(Select(PIC1[Lot No.], true) )
- For PIC2 [Qty] field, add App formula
sum(Select(PIC1[Qty],and([_thisrow].[Brand]=[Brand],[_thisrow].[Lot No.]=[Lot No.])))
- Create Detail View out of PIC2 table. Set Brand and Lot No. fields as QUICK EDIT.
Your user visit the detail view you created on step 7, and they select out of dropdown, then dynamically calculate value. but this PIC2 should be shared across the users. So if you want to keep this table assigned to each users, then turn on private mode on that table.