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:
Create a huge table containing all the fields used in the configurations of all products.
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.
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.
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.
Learn the possibilities for creating Conditional Drop Down Lists in the form widget (is not possible if I understand correctly).
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:
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;
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.
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.
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.
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.
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.
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.
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.
