Hello everybody! I'm wondering if there is a...

(Miranda Lubarsky) #1

Hello everybody!

I’m wondering if there is a way to reference a slice in an expression?

I’m trying to get around a cycle where a table is referencing itself and read in another thread from Praveen Seshadri that the only way this could be done is by referencing the slice instead of referencing the column in the original table.

What I am trying to do is for the initial value of a column to be whatever the input was in the previous row of that same column.

I created virtual columns to do the maxrow, and then created another virtual column as the “initial client name” and then referenced that column in the initial value field of the “Client Name” column, but it keeps coming up with a cycle.

So I created a slice of the table I am in, and am hoping to change the [maxrow] virtual column and [initial client] column to reference the slice instead of the original table to prevent the cycle.

But every time I try using “Design Numbers Reorganization Table Slice” in an expression in the column structure of the original column, it does not verify.

I’m wondering if I should be using some other punctuation/verbage to indicate that it is a slice, not the original table?

Thanks so much for your help! Miranda

(Steven Coile) #2

A slice is used exactly as a table would be.

(Steven Coile) #3

What you describe is not what I would call a “cycle”. A “cycle” (or “circular reference”) is when you have expressions in two columns that reference each other, such that a change in value in one would cause the other to change, which would cause the first to change, ad infinitum. Your description does not suggest the previous row’s value depends on the new row’s.

(Steven Coile) #4

@Miranda_Lubarsky [MaxCol] is the column containing the values you’re trying to find the maximum in. [_RowNumber], it would appear in your case

(Steven Coile) #5

@Miranda_Lubarsky How about this…

Create a virtual column named [PrevRow] with the App formula:

=maxrow(MyTable, _RowNumber, ([_RowNumber] < [_thisrow].[_RowNumber]))

(replacing MyTable with the name of the table you’re adding a new row to)

This column will accurately give you a Ref to the previous of each row.

The client name of the previous row can then be referenced as [PrevRow].[Client Name]

(Miranda Lubarsky) #6

@Steven_Coile Just tried this, got really excited…and it says it is still in a cycle!!!

(Steven Coile) #7

@Miranda_Lubarsky Try replacing the slice name with the name of the original table.

(Steven Coile) #8

@Miranda_Lubarsky Are you willing to give me read-only access to you app config?

(Miranda Lubarsky) #9

@Steven_Coile just tried that but its having trouble loading.

And yes! Definitely give you permission!

(Steven Coile) #10

@Miranda_Lubarsky Please do so and I’ll give it a look.

(Miranda Lubarsky) #11

@Steven_Coile how do i give you permission? Other than saying I give you permission lol?

(Miranda Lubarsky) #12

For what it is worth, the maxrow formula that I had used from Aleksi was working before, it was only when I tied it all together with the initial value field in Client Name column that it showed the cycle.

I have been working on this issue for a while and that is always what causes the cycle.

Not sure if that helps you.

(Steven Coile) #13

@Miranda_Lubarsky Right, since I’m not an AppSheet employee, you have to grant me access to your app configuration so I can see it. To do so, go to Share app in the left rail, to the Users tab. Click More settings to expand the box. Switch Add as co-authors? to ON. Enter my email address, sc2758g@gmail.com in the User emails box. Click Add users + send invite.

(Steven Coile) #14

Ah, I see. The maxrow() in the new row is identifying itself, so your expression is trying to look at its own, not-yet-existent value. This is, yes, a cycle.

(Steven Coile) #15

@Miranda_Lubarsky The maxrow() formula Aleksi gave you isn’t bad, it’s just not suitable for this particular use.

(Miranda Lubarsky) #16

@Steven_Coile ok wil do this asap.

Its being extremely slow reloading so will take just a minute.

(Miranda Lubarsky) #17

@Steven_Coile you have been added, lmk if it went through

(Steven Coile) #18

@Miranda_Lubarsky Yep, though I can’t see the app configuration. From that same Share app / Users tab, are there any options that might give me more access? Right now, all i can do is use (“preview”) the app.

(Steven Coile) #19

@Miranda_Lubarsky I’m in.

(Steven Coile) #20

@Miranda_Lubarsky Yikes! Slow to timeout!