Re: Trouble with an expression I already wrote but...

Miranda_Lubarsk · 10-02-2020 05:51 PM

Hey guys,

So I just regenerated one of my tables after a season of the app running smoothly, and I just lost one of my app expressions (actually a couple, but they all kind of function the same) that generated some info for me. No idea why it was lost - can someone please explain this to me so it doesn’t happen again???

My expression is this:

CONCATENATE((COUNT(SELECT(Timesheets[_THISROW], [Client Name] = [_THISROW]))), " OF “, [# of Visits], " VISITS”)

It verifies, etc. but doesn’t give me the answer I want it to give (and that is has given in the past).

Basically, I want appsheet to count the number of entries (ie. rows) in a given spreadsheet, that are assigned to a certain client. And then I want the answer displayed in the app like this:

“12 of 20 visits”

So as we make visits, the number of visits is tallied and we can see how many we have left.

So I am in a client info spreadsheet list, and I want the app to say we have made X number of visits out of the assigned 20 or whatever. This information is coming from another spreadsheet, titled Timesheets. It is linked via Client Name to the Client Info spreadsheet I am working off of. (The Timesheet spreadsheet lists a Client with each entry that is a Ref to the Client Name on the original spreadsheet.)

Each visit to the client has a new row in the Timesheet spreadsheet. So I just need it to count the number of rows, not a number in the column. And I need to filter it to just count the number of rows designated to that client.

The second part of it works (the 20 hours part).

But I"m having trouble getting it to add up the # of visits we have made thus far.

Please help - don’t know why it was working and then suddenly doesn’t work at all.
Many thanks!
Miranda

Miranda_Lubarsk

Having a similar issue with another expression that has worked in the past:

SUM(SELECT(Design Numbers Reorganization Table[Planting Only Price], [Client Name] = [_THISROW].[Client Name]))

It seems that the de-ref using the Client Name isn’t working. Thoughts??

Marc_Dillon

Generally, that shouldn’t happen. Had you made significant changes to the column structure of the regenerated sheet, like multiple name changes. That might have caused Appsheet to “lose track” of which column was which, and thus would have defaulted some of the column definitions. That’s just a guess.

If you have a Ref column like this, then you should also have a Virtual column in the Clients table called “Related Timesheets” (or similar). Your expression can be greatly simplified. Try something like this:

COUNT( [Related Timesheets] ) & " OF " & [# of visits]

Is this a similar structure as above, with a Ref column, and a “Related…” VC?

Steve

If an Initial value expression references a column that is removed or renamed as part of a column regenerate, the Initial value expression will be removed. There may be other column expressions similarly affected, but this is the one that gets me all the time. I’ve reported it internally several times, but haven’t seen a fix.

Steve

I wouldn’t expect Timesheets[_THISROW] to work. Perhaps you mean Timesheets[_ROWNUMBER]?

Miranda_Lubarsk

Hi all!! So, by accident, the Key Column [Client Name] type had been changed to Enum instead of Text. Once I fixed that, literally all the problems went away, thank god!!

Thank you for all your help!!

Any way to prevent it from changing something like that in the future?? If I have to regenerate?

Steve

By someone editing the app configuration? Or by the app editor itself (such as during a regenerate)?

Miranda_Lubarsk

I accidentally erased the column header [Client Name] and then when I figured that out, I typed the correct thing back into the app and Regenerated the Table, that’s when everything went wonky.

Trouble with an expression I already wrote but lost - COUNT function?