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.

  • 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 have this situation too, but I can work around via sql.

    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.

Login or Signup to post a comment