Re-write semi-complex spreadsheet formulas for in app processing

[Michael2]

The primary data table of this particular app has a field with concatenated data. This bulk of this data comes to me in this format and is unlikely to be changed. I need to separate the data into three fields. At the moment, the data is separated into the three fields through three spreadsheet formulas. Because of the spreadsheet formulas, the app must sync to compute the data before it is available to the app and the user. I would like to extract the data through appsheet rather than a spreadsheet formula to avoid having to sync.

The concatenated field is a of the text type with a comma as the delimiter. This data would need to be divided into as many as three fields but it may not need to be separated at all if it doesn’t have any concatenated data. For example the data could be “text1, text 2, text 03” or “text1, text 2” or just “text 03.” The one thing that will be consistent is if there is concatenated data it will have the coma delimiter.

The following are the spreadsheet formulas being used. Any suggestions would be greatly appreciated.

Concatenated data is in column Q “Circuits”

First Column is R, “Feeder1”
IFERROR(LEFT(Q2,FIND(",",Q2,1)-1),Q2)

Second Column is S, “Feeder2”
TRIM(SUBSTITUTE(MID(SUBSTITUTE("," & Q2&REPT(" “,6),”,",REPT(",",255)),2*255,255),",",""))

Third Column is T, “Feeder3”
IF(LEN(Q2)-LEN(SUBSTITUTE(Q2,",",""))>1,RIGHT(Q2,LEN(Q2)-SEARCH("#",SUBSTITUTE(Q2,",","#",LEN(Q2)-LEN(SUBSTITUTE(Q2,",",""))))),"")

@Steve

[Steve]

I’m not going to try to interpret your spreadsheet formulas.

Questions:

1. If the original “concatenated field” has no comma, is it safe to say the only value it contains is text 03?

2. If the original field has a comma, is it safe to say it will contain text 1, text 2, and (possibly) text 03 in that order?

3. Is the delimiter a comma (,), or comma+space (, )? Or does it vary?

[Michael2]

1. Yes
2. Yes
3. There may or may not be a space. It does vary.

[Steve]

Try this…

Add a column to receive the split values, with an App formula expression of:

IF(
  CONTAINS([concat-field], ","),
  SPLIT(
    SUBSTITUTE(
      TRIM([concat-field]),
      ", ",
      ","
    ),
    ","
  ),
  LIST("", "", [concat-field])
)

replacing concat-field with the name of the column containing the concatenated field.

Then make the App formula expression for the three columns to receive the individual component values:

INDEX([concat-split], 1)

INDEX([concat-split], 2)

INDEX([concat-split], 3)

where concat-split is the name of the column from above.

Test! Test! Test!

[Michael2]

Couple of issues:
Concat-Field is an EnumList with a base type of Text. This base type seems to be ignored when I create the concat-split field because AppSheet throws an error about mismatched types although concat-split is set as a text type. For testing I have set concat-field as a text type to match concat-split. Initially, I tried to set concat-field as a list type but list type columns are disallowed with real columns.

Your expression to split the concatenated field seems to work BUT… when I add the INDEX([concat-split],1) AppSheet throws an error that INDEX has invalid inputs?.. I’ve read the documentation (which you wrote) for the INDEX function and, as best I can tell, this expression is valid.

[Steve]

Michael:

Concat-Field is an EnumList with a base type of Text.

What?! It’s already a list??? Now I don’t understand what you needed help with originally…

[Michael2]

Okay…

The primary data initially comes from a GIS db. The data pertains to utility poles upon which electric distribution circuits are attached. Within this data is a field of concatenated circuits which are attached to the pole. There could be as many as three attached circuits concatenated in this one field.

This data is downloaded from the GIS db as the poles for each circuit need to be inspected and imported into the AppSheet app. While the primary set of data is downloaded from GIS, there is a possibility that the inspector will come across a pole in the field that is not in the data. At that point he would add a new pole. I can allow him to do so and pick the circuits via an EnumList but…

Because the work (circuits to be inspected) is disseminated to multiple inspectors by circuit, I need/want to use security filters to determine which circuits are pushed in the app to an inspector. To use security filters, the data upon which the security filter is run must be a real and not virtual column hence the need to split the concatenated fields.

[Steve]

My turn to clarify…

If concat-field is already an EnumList of Text, its individual components should already be available–assuming the stored value is properly formatted.

How is the concat-field getting its value? Are you importing a CSV file? Is some other process writing it?

Can you post the following screenshots?

• A sample of the concat-field spreadsheet column.

• The app’s concat-field column configuration screen showing the top down to and including the Type Details section.

[Michael2]

The data is initially imported via CSV. Once imported nothing other than Appsheet is interacting with the data. There is no formula in the concat-field as the data is concatenated BEFORE it is imported. When the data is received, the field already contains the data concatenated.

Once the inspection begins, the user will have the ability to add rows to the same table. He/she does not see this concatenated field. Instead, the inspector is shown three separate fields in which to enter circuits: FEEDER1, FEEDER2, FEEDER3.

At the moment, spreadsheet formulas are being used to split the concatenated data (THAT WAS IMPORTED) into these three fields FEEDER1, FEEDER2, FEEDER3.

An example of the data contained would simply be: “100, 101, 102A”

[Steve]

Change the type for the CIRCUITS column from EnumList to Text, then try the expressions I gave you.

As you’ve described it, the CIRCUITS column is not an EnumList value, and isn’t used as such, so it shouldn’t be configured as one. Further, if its values are coming from a CSV, you don’t need Valid If, Invalid value error, or Suggested values configured.

[Michael2]

Thank you Steve for your patience while helping me work through this.

The CIRCUITS field is an EnumList because:
When the data is INITIALLY imported, it can contain as many as three circuit. If that were the end of the process, then yes, this field would need to be set as a text type. BUT…

Because the inspector may encounter a pole in the field that is not contained in the imported data, he will need to add this pole to the same table. When entering the information for the circuit, he is shown a list of circuits to choose from via Suggested Values and his entry is limited to picking 3 circuits through the Valid_if.

I previously tried changing this field to text only but still receive the error that INDEX has invalid inputs.

[Steve]

Does any user ever modify the value of the CIRCUITS column itself from the app?

[Michael2]

Yes, IF he is entering a new pole found in the field that is not already present in the data.

[Steve]

So he does not use FEEDER1, FEEDER2, and FEEDER3?

[Michael2]

Not the way the app is currently setup. In those fields, spreadsheet formulas are used to split Circuits into the separate Feeders.

He could use the Feeder fields to enter the circuits that are attached to the pole BUT, I still need to separate the concatenated data when data is IMPORTED. I cannot change that fact.

[Michael2]

I’m rethinking this…

The CIRCUITS field should be a text column. Then your solution should be able to be used.

When the user enters a new pole in the field, he should be using the FEEDER fields and not the CIRCUIT field.

Standby while I restructure my thoughts and the app…

[Michael2]

I’m not having the luck I’d hoped for.

I setup a test app if you have time/ feel like checking it out. Don’t remember your email to add as an editor…

At the moment, all fields are real columns.
All fields are set as text types.

The expression to parse the index complains about invalid input.

[Michael2]

Okay, it’s partially working now BUT

Your solution works when importing data if the INDEX expression is an App formula. It does not work if the expression is set as the initial value. The problem now is, if an app formula is set on the field, the data cannot be edited by the user. If I am to use these fields for field entry of new poles, I cannot also use an app formula.

[Steve]

Set the Initial value expression to my expression, then set Reset on edit? to the expression:

ISBLANK([_THIS])

[Michael2]

No dice.

When set as the initial value, the expression seems to be ignored even though Reset on edit is set to ISBLANK([_THIS]).