Skip to main content

I tried to use rollup function to got previous data in the same table as below

First, I make the lenth of one field to be the same.

IF(LEN({TargetField})>10, LEFT({TargetField}, 10), {TargetField}&REPT("_", 10-LEN({TargetField})))

Then in a linked filed

I use Rollup functon ARRAYJOIN(values) in another table

Then look up the Rolluped field in the first table.

but When I tried to figure out the length of the lookup-ed rollup filed

It showed “error”


as example

Table one

(number field) (target filed) look-uped rollup filed

1 a 123 a-123 a-123,b-456,c-123,d-456

2 b 456 b-456 a-123,b-456,c-123,d-456

3 c 123 c-123 a-123,b-456,c-123,d-456

4 d 456 d-456 a-123,b-456,c-123,d-456


in second row

I tried to find the location of the word “b”

and the number between 2 to 4 characters before “b” is the data I want.

However , when I use Find or search function to the lookuped roll-up field

It’s always showed 0


Do anyone know what wrong it is, or how to mkae it in alterntive way…

Lookup fields often return arrays, even if there is only a single element in the array. I’d start by appending an empty string — &'' — to your lookup field and see if that clears the error message. Appending an empty string to a lookup field casts it as a string:


{LookupField}&''

Lookup fields often return arrays, even if there is only a single element in the array. I’d start by appending an empty string — &'' — to your lookup field and see if that clears the error message. Appending an empty string to a lookup field casts it as a string:


{LookupField}&''


Thanks, it works

Here is what I do to make this

let a record of weekly note show the note of last week as reference.




  1. Add a string that is unique to the beginning of this week’s note

    For example first field of this recored - {Name}

    Formula =

    {Name}&{weeklyNote}




  2. and make each note have the same length of words. like 100

    Formula =

    IF(Len({Step1})>100, LEFT({STEP1}, 100), {STEP1}&REPT("_", 100-LEN({STEP1})))




  3. In another table linked to this table, Rollup Step 2 with

    CONCATENATE(Values)




  4. In the original table, Lookup the Value of STEP 3.




  5. Thanks to W_Vann_Hall, add a space to STEP 4

    {STEP4}&" "




  6. Note of last Week

    RIGHT(LEFT({STEP5}, (FIND({NAME}, {STEP6}))-1), 100)




I think it can also be used to got previous data as number or others.

Thanks, it works

Here is what I do to make this

let a record of weekly note show the note of last week as reference.




  1. Add a string that is unique to the beginning of this week’s note

    For example first field of this recored - {Name}

    Formula =

    {Name}&{weeklyNote}




  2. and make each note have the same length of words. like 100

    Formula =

    IF(Len({Step1})>100, LEFT({STEP1}, 100), {STEP1}&REPT("_", 100-LEN({STEP1})))




  3. In another table linked to this table, Rollup Step 2 with

    CONCATENATE(Values)




  4. In the original table, Lookup the Value of STEP 3.




  5. Thanks to W_Vann_Hall, add a space to STEP 4

    {STEP4}&" "




  6. Note of last Week

    RIGHT(LEFT({STEP5}, (FIND({NAME}, {STEP6}))-1), 100)




I think it can also be used to got previous data as number or others.


There’s another way to identify and display the most-recent ewhatever], as @Matt_Bush explains in this reply Your method is awfully clever, though…

Reply


Terms & Conditions