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

[Miranda_Lubarsk]

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]

A slice is used exactly as a table would be.

[Steven_Coile]

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]

@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]

@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_Lubarsk]

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

[Steven_Coile]

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

[Steven_Coile]

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

[Miranda_Lubarsk]

@Steven_Coile just tried that but its having trouble loading.

And yes! Definitely give you permission!

[Steven_Coile]

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

[Miranda_Lubarsk]

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

[Miranda_Lubarsk]

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]

@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]

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]

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

[Miranda_Lubarsk]

@Steven_Coile ok wil do this asap.

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

[Miranda_Lubarsk]

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

[Steven_Coile]

@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]

@Miranda_Lubarsky I’m in.

[Steven_Coile]

@Miranda_Lubarsky Yikes! Slow to timeout!

[Steven_Coile]

@Miranda_Lubarsky Let’s get the app working first. Would you like me to make changes (since I have that level of access), or provide recommendations?

[Miranda_Lubarsk]

@Steven_Coile just made you admin.

Yes please! i def think it’s the maxrow expression causing problems.

It was fine before i messed with that

[Steven_Coile]

@Miranda_Lubarsky My thinking is that the use of a virtual column for this is the wrong approach.

The value of the [MaxRow] virtual column is computed for each and every row in the table. If the table is large, it’s a large number of computations. The maxrow() function is going to take a long time if the table is large and the function is run for each and every row.

I will disable the [MaxRow] column to allow the app to function, then we can tackle this problem another way.

[Steven_Coile]

@Miranda_Lubarsky The app is loading again. Please confirm it’s working okay.

I disabled the [MaxRow] and [Initial Client Name] columns.

[Steven_Coile]

In the filter argument for your maxrow() expression, explicitly omit the current row:

maxrow(MyTable, MaxCol, ([KeyCol] <> [_thisrow].[KeyCol]))

[Miranda_Lubarsk]

@Steven_Coile it works! Thanks! So what do you propose we do? Cannot thank you enough for helping me!

[Steven_Coile]

@Miranda_Lubarsky I have an idea, but need to look at a few things first. Stay tuned…

[Miranda_Lubarsk]

@Steven_Coile ok awesome!!

[Steven_Coile]

@Miranda_Lubarsky I’ve put something in place that attempts to provide an initial value for [Customer Name], [Plant ID], and [# of plants] by doing everything from the Initial value expression, without using a virtual column. Unfortunately, I don’t know if the results make sense. Please review and let me know.

[Steven_Coile]

@Miranda_Lubarsky The solution I’m trying avoids introducing virtual columns that need to be computed for every row whether needed not not. Instead, the initial value uses existing data and is only constructed when a new row is added.

[Steven_Coile]

@Miranda_Lubarsky My attempted solution finds the maximum [_RowNumber] value, then uses that to pluck the desired column value from that highest-numbered row for use as the initial value.

[Steven_Coile]

Lunch time!

[Miranda_Lubarsk]

@Steven_Coile Steven,

I am not seeing an initial value for the Client Name which is what I need it for.

I don’t need any initial value for the Plant ID or plant number…these are all items that will be changing with each input.

It is the client name that will be repeated a bunch of times, and so that’s why I wanted it to automatically pop up without people having to scroll through the whole client list.

Does that make sense?

[Miranda_Lubarsk]

@Steven_Coile I like your idea!!

Just not seeing it happening in the app.

I see it in the initial value field…it’s just not effecting the app as far as I can tell.

[Miranda_Lubarsk]

@Steven_Coile Oh wait!!

I think it did work!!

Just not the first time, but if I entered something, then it repopulated the second time!!!

Happy happy lunch!!

You are my hero!!! Wow wow wow.

Omg thank you SO SO SO much!!!

[Miranda_Lubarsk]

@Steven_Coile it says the error is a cycle.

The initial vlaue refs the [Initial Client Name] which is a virtual column with appformula =[MaxRow].[Client Name].

Then the [MaxRow] is also a virtual column which has appformula =(Design numbers Reorganization Table, _RowNumber) with type Ref of the slice, but it was the original table before.

It still says “Y”/nExpression appears to be part of a cycle.

And how exactly would I write out the name of the table in the expression.

Because it was not verifying or recognizing it.

[Steven_Coile]

@Miranda_Lubarsky Understood. Feel free to remove the initial value for the 2 other columns. I’ll resume looking at [Client Name] after lunch.

[Steven_Coile]

@Miranda_Lubarsky To test, please add several new rows: 2 in a row with the same customer name, then 1 with a different name. Let me know if there initial values are as you want in each case.

[Steven_Coile]

@Miranda_Lubarsky Done with lunch, but unless you notice any problems, I don’t think there’s anything left that I need to do.

My remaining recommendations:

• Replace the select() call in the Client Name initial value expression with Design Numbers Reorganization[_RowNumber]. The rest of the select() is unnecessary.

• Entirely remove the [Initial Client Name] and [MaxRow] virtual columns, as they would appear to now be unnecessary.

• Remove my access to app configuration.

Thank you for the opportunity to exercise my skills! Let me know if I can be of further assistance!

[Steven_Coile]

You shouldn’t need a slice to accomplish what I think you’re trying to do.