AnsweredAssumed Answered

Using 'LEFT' function (or similar) to lookup corresponding choice list

Question asked by siewwei.goh on Nov 3, 2017
Latest reply on Nov 6, 2017 by siewwei.goh


I would like to use something similar to Excel expression, "LEFT" function, to refer to part of the Asset ID which will then automatically populate subsequent fields.


For example, as shown in the diagram below for Asset ID OBN51581A


How can I structure the XLS form to get it to calculate or lookup the specific string from an Asset ID so that I can

and bring up the corresponding 'name' field listed in the choices tab?

So, my questions are:

  1. How do I write the query to look up parts of a string - the command line to extract/look up part of Asset ID
  2. And then get it to work with choice list and/or using choice_filter given it will be a one-to-one relationship?


An example of my choices sheet will be something like:


I know I can use pulldata to grab the same information, however, using the external CSV dataset is somewhat rigid and required the every asset information to be correctly entered in the CSV file. 


Any suggestions will be greatly appreciated.


Thank you