Many-to-Any Relationships

Grist Feedback
3

Thank you natalie-grist
The task of this configurator is to calculate the PartNo (Final combo in you example)=use as UUID field using a formula based on the selected values ​​of Type, T_M, T_M_V and CT.

  1. If i prepare data in excel for this form, the same as you, I make a table using cross Join in Power Query of all possible matches of Type, T_M, T_M_V and CT tables and calculate all Final combo as in you example, and delete unused matches.
  2. In the form I use Conditional Drop Down Lists

I provided this example for the purpose of:

  1. Learn the possibilities for creating Conditional Drop Down Lists in the form widget (is not possible if I understand correctly).
  2. Learn the possibilities for creating cross Join for generate all possible matches.

And the next thing if I have many such configurators=different tables and form widgets (config_1, config_2) with different field sets but all of them are united by the fact that they configure the Description and the PartNo to result_table with columns ‘Description’, ‘PartNo’, ‘Price’ and then use this data for add configured products in invoice.
Product_manager_1 add price use config_1 form widget
Product_manager_2 add price use config_2 form widget
Sales_manager work with invoice view.

In this case i have several ways:

  1. Use UNION
    Create config_1 and config_2 as cross Join tables
    Generate result_table by
SQL
SELECT Description, PartNo, Price FROM config_1
UNION
SELECT Description, PartNo, Price FROM config_2
ORDER BY PartNo;
  1. Create multiple views of the same data from one huge result_table which has any columns from config_1 and config_2 tables and create config_1 and config_2 views.
  2. Use M2A princip
    result_table with columns ‘Linked_widget’ ,‘Description’, ‘PartNo’, ‘Price’
    Where ‘Linked_widget’ is column for dynamicaly calling saved name of form widget where this record is created.

Sorry, English is not native, I tried to explain in as much detail as possible.