How to split text string in a data source, so it shows as a choice field in the app?
I have a data source, where one column has a series of information. Example attached.
The description column shows per row a series of line items: 12 | A - 099 | 1 12 | B - 099 12 | C - 099
Where the first description is 12 | A - 099 | 1
the second 12 | B - 099
and the third 12 | C - 099
Each line item contains a pipe separator "|" and between descriptions there's space " ". Since space is available inside each description, thus not unique, I wonder if it's possible to split the text by using the very first info before the first pipe separator. In our example, "12".
Perhaps there's a formula that I could use?
Any help would be much appreciated.
(16.3 KB)
Can I ask: why wouldn't you put each of those items in different columns in your data source, instead of mixing data in a description?
Thank you for your suggestion, Victor. In this particular case, I'm not sure it can be possible since the data source is built through Zapier. When creating a Zap you need to select the exact info you'd like to use. Description has it all in one string. Even if I choose to repeat it a few times (some can contain up to 10 line item descriptions), it will not help the user that much, who will have to manually delete the unnecessary ones. A choice field would be the ideal way, but I don't know how to break the different descriptions.
To give some background information, the descriptions belong to an estimate. However, I don't have unique data in an estimate for each line item description. The reason why I don't know how to break the line item descriptions into a data source.
I am thinking that what if you could have a new table in QB, and connection from ZAP for the choice field.
You might be able to manipulate in the connections page and template, but long shot.
Robert, thank you so much for the suggestion. I appreciate you. I have looked into that possibility, but cannot add that many fields into QB that can correspond to each description.
Hi Gabriela, is this something you've managed to resolve?
Thanks for checking. Unfortunately not. We needed to have a unique value for each line item description, to be able to separate them in the FOF data source. Interestingly enough, the zaps we had in place worked great if we were to link directly through Google Sheets. However, because there is a 15 min update delay when using Google Sheets as a connector for the FOF data source, we abandoned pursuing this option.