Form value populated based on previous selection

[Sarah_Keown]

Hi, I need a form value to populate based on the previous selection from a dropdown.

I have the following 3 columns; Product - Product Code - Product Description within an excel sheet. The Product column is populated through the concatenation of Product Code and Description. You can see an example of this below.

I have multiple sheets with the same column information, except the product groups are different. In the form I select my Product Group which then allows me to select from a list of Products associated to that group; the dropdown shows the Product column from the spreadsheet i.e. the concatenated values.

Underneath the product group and product dropdown items in the form, I want to populate the following ‘Product Code’ form value with the product code associated with the product they have chosen. So, much like I have filtered the product groups and products, I want the product code for that selected row to appear in the form. For example, if a user selects ‘HD1/10W-Hep20 Pb Staright Connector WT 10’ the product code ‘HD1/10W’ will appear in the product code form input.

I have attempted this using various SELECT() and FILTER() statements, but because the product groups are across various sheets in excel I cannot find an expression that is universal to the situation; I have to refer to a specific dataset(sheet) in excel but this process must work across all groups of products.

If there are any possible approaches to this, I would be appreciative of any advice.

Thanks

[Aleksi]

To understand better your table/column structure, what formula are you using when selecting the Group and Product?

[Sarah_Keown]

When selecting the Product Group: Groups[Group]
This returns a dropdown based on a list of values in ‘Groups’ excel sheet.

When selecting a Product from a group: I am using an IFS statement. Like the example below;
IFS(
[Product Group] = Malleable Black Iron Fittings ,Malleable Black Iron Fittings[Product],
[Product Group] = Hep2O Pipe and Fittings,Hep2O Pipe and Fittings[Product]
)

I have made some progress populating the Product code using the LEFT() expression, however I cannot specify explicitly how many characters from the left fragment I wish to show as all Product Codes are of different lengths.

I tried to use LEFT([Product],LEN([Product Code])) in an attempt to grab the Product Code concatenated in Product, based on the length of the Product Code. However, this method removed Product Code from the form.

[Aleksi]

You can use SPLIT expression like INDEX(SPLIT([Column],"-"),1) to read your first part.

[Sarah_Keown]

Thank you! Using Split in this way worked; I was previously attempting to do it on a Text type instead of list type I think that was the issue.

[Aleksi]

The SPLIT expression is meant for string to create a list… so it should work with the text.