Many-to-Any Relationships

[BiBo]

Hello, I have an idea for building CPQ based on Grist.
There is a general table of all products with common fields for all products (UUID, Name, Description, PartNo).
But each product is configured using data from different tables and different forms. For example
This can be done in several ways:

1. Create a huge table containing all the fields used in the configurations of all products.
2. Create M2A as Directus doc’s

Both of these options require Widgets to be loaded dynamically.

I have a question, maybe this is already implemented to save link to Widgets in table and call linked widgets dynamicaly?

And the third option
Create separate tables for product types and use UNION for create result products table (or view).

[natalie-grist]

Hey there!

You may be able to do this with custom widgets but it will require quite a bit of coding skills. Link Keys may also be of help.

I built an example for you using the example you provided. Check it out at the link below.

https://public.getgrist.com/1L23XQHxWhS2/Community-650/p/6/m/fork

On the selector page, you’d select type then material updates based on type selected. Then Clock Setting / Voltage would update based on material and type. Last, Connection Technology would update based on the three selected prior.

To set this up, I created a table of ‘Type and Material’ then ‘Type, Material, Voltage’ and then ‘Type, Material, Voltage, Connection Tech’ - each building on the last. Each table has a Building Link column (this is the union result you mentioned) which links to the next table. For example, Building Link in the TM table is referenced by Link to TM in the TMV table. This allows the widgets to update dynamically.

It takes a bit of time to set up. I would build a table then copy/paste into my next (e.g. built TM, copy pasted into new TMV table) then add rows to build out the next selector. You’ll see in Connection Technology where I only completed about half. Quite a few combos still need to be updated with the Connection Technology options available.

Hope this helps!

[BiBo]

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;

2. 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.
3. 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.

[natalie-grist]

Grist does not have conditional drop down lists but it is a feature we would like to implement.

Our cross join is a few steps but way easier than the first way I sent! Always learning new things Grist can do! I updated the example with two new tables; All Options and Cross Join.

https://public.getgrist.com/1L23XQHxWhS2~3rXiLFYJbwf6Tw27sVEWk6~14207/Community-650/p/11

In each column of the All Options table, we have a reference list for each option; Type, Material, Clock Setting / Voltage and Connection Technology. In each column, we have a formula in the following format:

TABLE_NAME.all

Previously, I set up a table for each option with the different choices available. That’s what we are using here. Below, you see that the equation for the Clock Setting / Voltage column is Clock_Setting_Voltage.all and the column type is Reference List pulling data from the Clock Setting / Voltage column of the Clock_Setting_Voltage table. We see all options listed in the cell.

Once you have this page set up for all options, you can cross join by making a summary table and group by all options

This is the ‘Cross Join’ page I created. It only includes this summary table.

I added the Combo column that combines the values in each column.

Next, we want to detach the data. In the Table Configuration menu on the right hand side, navigate to the Data section and then select ‘Detach’.

This turns our summary table into a regular table with all of our data! Now, you can name the table and call it in formulas elsewhere in your document.

[BiBo]

Thank you!
And not possible create UNION?

SQL
SELECT Description, PartNo, Price FROM config_1
UNION
SELECT Description, PartNo, Price FROM config_2
ORDER BY PartNo;

[natalie-grist]

Unfortunately it’s not possible to create UNION. Grist does not yet offer a convenient tool for combining data in separate tables. I’m not sure if it would be useful in your case, but you could keep all configurations in a single table and add a column that assigns it to a configuration. This way you could sort or filter by configuration. You can also sort by Part No so all part no configurations would be listed together.

[BiBo]

This question intersects with

How can I reference data across documents and workspaces?

I have 4 workspaces, as I divide between my clients. In my master workspace, I use common data (eg Country list). How can I use that data as Reference in a document in a different workspace? Currently I maintain the same table copied in different workspaces/documents, but would be good to have a single source of truth to avoid mistakes.

If i have 3 different tables (with columns):
ProductType1 (UUID, ProductName, PartNo, ColumnsN)
ProductType2 (UUID, ProductName, PartNo, ColumnsM)
AllProducts (UUID, ProductName, PartNo)
In each ProductType table I configure PartNo and ProductName for this product type.
Then I need to union all the data from the table ProductType1 and ProductType2, while making changes to any ProductType tables need same result in AllProducts table.

This can be done on the basis of one table ProductType (UUID, ProductName, PartNo, ColumnN, ColumnsM) but formula for calculate ProductName and PartNo based on ColumnsN for ProductType1 and ColumnsM for ProductType2.
That is, if I have N ProductType i need N conditions for formula calculated ProductName and PartNo.