Solved: Expression to split these items into rows?

Tiger1 · 03-25-2020 09:49 AM

I need a good expression to split this paragraph up (if possible).

Instead I would like a row per - item - starting with (1) or (2) etc… Like this:

Marc_Dillon

I think you need to do this:

*<<INDEX(   SPLIT(   [ITEM_LIST] , "*" ) , 2 )>>*
<<INDEX(    SPLIT(   INDEX(   SPLIT(   [ITEM_LIST] , "*" ) , 3 ) , "," ) , 1)>>
<<INDEX(    SPLIT(   [ITEM_LIST] , "," ) , 2 )>>
<<INDEX(    SPLIT(   [ITEM_LIST] , "," ) , 3 )>>
<<INDEX(    SPLIT(   [ITEM_LIST] , "," ) , 4 )>>
<<INDEX(    SPLIT(   [ITEM_LIST] , "," ) , 5 )>>

View solution in original post

Steve

Start with this, in another thread with you:

Tiger1

I tried that. It didn’t give me row per row:

That is using the expression you had given me.
NOTE - i made sure it was two lines…

Steve

The column receiving the result must of type LongText.

Tiger1

oh ok. It was “text” ill make the change and see what happens. Thanks again Steve, as always!

Tiger1

I changed “Item_List” to type LongText - it still gives exact same results.

Steve

Screenshots, please.

Tiger1

Expression in my Google Doc:

Result:

Table:

Steve

Hmm… I honestly don’t know if that should work in a template. I’ve only ever used it within the app itself. You could try creating a virtual column with the SPLIT() expression as its app formula, the use that in your template rather than the SPLIT() expression.

Tiger1

ok. Thanks Steve. I used SUBSTITUTE - not SPLIT. Do you mean use Split() instead?

Steve

Whoops! Yes, I should have said SUBSTITUTE(). Sorry for the confusion.

Tiger1

I gotcha. So i tried it - same result. Could i just set up a bunch of SPLIT() expressions - and hopefully have enough to fill enough rows?

<<Index(SPLIT([Item_List],“,”),1)>>

etc…

Steve

You could certainly try. It’ll be ugly.

Tiger1

ok. Thanks

Tiger1

It looks ok. But i have a question. How can i make the “Rental Return” be at the top ONLY. Then each row follows under neath?

I tried and this is what i got - “*Rental Return” - shows up twice?

Steve

I don’t know what your current template or expressions are.

Tiger1

Template:

So that top expression - is a little different because the data at the beginning has a “*”.

For example:
" * “Rental Return” * " - Well you cant see - but that has **** on both sides of it…

Steve

*Rental Return occurs twice because both of your first two expressions produce it:

<<Index(SPLIT([ITEM_LIST], "* "), 1)>>
<<Index(SPLIT([ITEM_LIST], ","), 1)>>

Tiger1

How do I make Rental Return be the first line only. And then each line after…?

Also I thought the "* " was different than the ", "

Shouldnt it look at the " * " differently than ", "?

Meaning as two separate lines

Should it be:
<<Index(SPLIT([ITEM_LIST], "* "), 1)>>
<<Index(SPLIT([ITEM_LIST], “,”), 2)>>

Steve

There’s no easy way to do it. Your input isn’t well structured for the tools AppSheet provides. I’m still looking at it, though.

Tiger1

It still looks pretty good:

But i just wish i could move that first row - After the “None” down…

Steve

Try replacing those first two with these:

<<INDEX([ITEM_LIST], "* ", 1)>>*
<<INDEX(SPLIT(INDEX(SPLIT([ITEM_LIST], ","), 1), "* ", 2)>>

Tiger1

I think i am missing a “)” in your second line?

Also in the first line - do i put the “*” at the end?

Steve

Yep, missing parenthesis. Corrected:

<<INDEX(SPLIT(INDEX(SPLIT([ITEM_LIST], ","), 1), "* "), 2)>>

The extra * is to replace the one SPLIT() removes, so you get *Rental Return* instead of *Rental Return.

Tiger1

Ok. Let me try this. BTW - I wouldn’t have come close to what you have. Thanks so much…

It doesnt fire the workflow rule now. I am guessing the expression has an issue?

Steve

Eek! Another typo, this time in the first expression. Try this:

<<INDEX(SPLIT([ITEM_LIST], "* "), 1)>>*

Tiger1

ok. It runs now. But i don’t see the ", " forcing a new line, see image:

Shouldn’t those items (i circled) be a new row?

Oh wait - i just realized - it has the same data twice. So your first two rows are all i should be using? If so, how come it doesnt put each on a new row?

Steve

It’s a problem with my second-line expression. Hold…

Steve

I don’t see a problem with it, but let’s try this instead:

<<INDEX(SPLIT(INDEX(SPLIT([ITEM_LIST], "* "), 2), ","), 1)>>

Tiger1

It looks the same to me:

I will say this. You got the first part on the first line:
“Rental Return”

Steve

Whoohoo!

Steve

Hmmm… It works for me from Expression Assistant. Please paste the text of that entire rental return message into a reply so I can test with it.

Tiger1

the template? the result is an email.

Steve

No, the text the template is processing.

Tiger1

Rental Return (1) [000000183] 2006 16’ LOWBOY TRAILER - VIN # 5FVBU16296B008780 - PLATE #625327H ,(1) [000000272] 2012 16’ LOWBOY TRAILER - VIN # 17XFH1625C1026074 - PLATE # 72114N ,(1) [000050077] 4,000 PSI HOT WATER PRESSURE WASHER SKID ENGINE: D1005-1GN0859,(1) [0706-35] 4,000 PSI HOT WATER PRESSURE WASHER SKID ENGINE: D905-6A2663,(6) [R-2 HOSE 50FT] 50FT R-2 PRESSURE HOSE,(2) [TRIGGER GUN] TRIGGER GUN

Tiger1

That? That is the ACTUAL column value for [Item_List]

The first part “Rental Return” has * on both sides - FYI

Steve

Thank!

The expressions appear to work correctly.

In your template, please remove all of the INDEX() expressions then add them back one by one, testing after each, until the output isn’t what you want. Then post a screenshot of the expressions in use and the output.

Tiger1

Ok - i took out the first 3 “index” and it wouldnt fire until this one was put back. However - still not putting each row by row:

Tiger1

Thanks Steve for all your help on this!

Steve

It does, but every time you use SPLIT(), you tell it to look at the entire original input list. The parts you’ve already seen are still there in the original.