Delete a row from a calendar form

Sven1 · 11-10-2020 03:46 AM

Hi all,
so I am/was quite profficient with database design and application development back in the days, and I am struggling with AppSheet

I have 2 data sources: Calendar and Sheet (“Teilnehmer”).
Calendar holds mountainbiking events and the sheet “Teilnehmer” the people who sign-up for this.
Calendar and sheet are linked using the Calendar Id.
I also created a slice called “MeineAnmeldungen” which filters “Teilnehmer” on ([Email] = USEREMAIL()).

In the Calendar Form, I have added a behavior that creates an entry in the sheet, using the Calender data.
LINKTOFORM(“MeineAnmeldung_Form”, “Datum”,[Start],“Ausfahrt”,[Title],“Email”, USEREMAIL(), “AusfahrtId”, [Row Id])
As you can see, I am populating the sheet with the calendar Id and with the current user mail.
That works like a charm.

Now from a UX-perspective, I would like to have the same kind of button which will delete the row.
I have created a new behavior with the following settings:
Action Name: Delete
For a record of this table: Ausfahrten (the Calendar data table)
Do this: Data: execute an action on a set of rows
Referenced table: Teilnehmer (the sheet)
Referenced rows: FILTER(“MeineAnmeldungen”,([AusfahrtId]=[Row Id])) (basically I am using the slice that filters users based on their login, and the condition that I want to delete the row matching the calender Id of the event).
Referenced action: Delete

It looks awesome, but it doesn’t work. No error is thrown, but no line is deleted either.

When testing it, I see a “2” in the “Result” column.

What am I doing wrong?

Thanks for your help!
-s

Suvrutt_Gurjar

Without going into minute details, I request you to try either of the following expressions
FILTER(“MeineAnmeldungen”,([AusfahrtId]=[_THISROW].[Row Id]))

or the following

FILTER(“MeineAnmeldungen”,([_THISROW].[AusfahrtId]=[Row Id]))

Edit: Change in second expression suggested.

Sven1

Thanks!
Unfortunately the second expression does not work at all:
Error in expression ‘[Row ID].[AusfahrtId]’ : Unable to find column ‘AusfahrtId’

The first expression you posted leads to the same exact behavior as before: looks nice, doesn’t work…

But looks like you are confirming that using a Slice of the “Teilnehmer” table should work.

The question is: why it doesn’t it delete anything?

Thanks,
Sven

EDIT: here is another expression I used (to avoid using the slice):
FILTER(“Teilnehmer”,(([AusfahrtId]=[Row Id]) AND ([Email]=USEREMAIL())))

Doesn’t work either.

Suvrutt_Gurjar

Hi @Sven,

Thank you for update. As I mentioned in my post earlier, I suggested an expression without going into minute details. Of course, I suggested to add [_THISROW] so as to evaluate the expression in the context of current row.

I will go through the details you have given and revert with either an approach or may be queries if I have. If I am unable to find a solution, I will mention that as well.

In the meantime, someone else may help you.

Sven1

Much appreciated. I know how hard it is to help remotely!

If it helps, here is my current action:

Could it be that I am missing some rights somewhere?

Generally speaking, looks like I cannot “debug” or “trace” to see what is going on, right?

BR,
-s

Suvrutt_Gurjar

Hi @Sven,

In general, your implementation looks good on my testing on a similar setup.

Please try using following expression in the Abmelden action for the referenced rows.

SELECT(MeineAnmeldungen[Key Column of Teilnehmer ], [AusfahrtId]=[_THISROW].[Row ID])

I presume [AusfahrtId] stores the Calendar table’s ID and the key column of Teilnehmer table is not [AusfahrtId]

Also please ensure the slice MeineAnmeldungen has deletes enabled in slice setting.

Also any specific reason you are using IN([Row ID], MeineAnmeldungen[AusfahrtId]) in action condition? I believe since referenced rows are already qualified, do you need one more condition?

Sven1

Hi @Suvrutt_Gurjar,

Ok, so I did a couple of checks.

I get a warning saying: " Table ‘Teilnehmer’ has an implicit (RowNumber) key – if multiple users insert or delete entries concurrently, data loss can occur. Updates may apply to the wrong record or could fail entirely on some data sources."
The “MeineAnmeldungen” slice has delete enabled in the settings:

To your points:
I tried using your expression, no success, as follows:
SELECT(MeineAnmeldungen[_RowNumber], [AusfahrtId]=[_THISROW].[Row ID])
You’re right, the key column of “Teilnehmer” ist not “AusfahrtId”. I left it to be the row number/row id.

In the action condition I want to show/hide the button based on the condition that the user has created an entry for the current Calender Id in the Teilnehmer sheet / MeineAnmeldungen slice.

Moving a step further: if I look at the test results, it actually looks fine (IMHO).
The results corresponds to the rowId of the Teilnehmer sheet which corresponds to the AusfahrtId from the calender ID (cannot paste a second screenshot here).

That is a tough one… thanks for your time!!

Sven1

I have the feeling this test result might help, so I upload it in a response.

Edit: I just did a last, simple test: I hardcoded the “referenced row” to “List(3)” in the action just to test if a row was deleted in “Teilnehmer” sheet. Did not work either. So there must be something else I am missing. Is the “For a record of this table” (set to the “Ausfahrten” Calendar table) OK like that? If I change it to “Teilnehmer”, the button does not show up anymore…

BR!
-s

Steve

You aren’t alone…

Sven1

Thanks for your feedback Steve
So you may have an answer to the latent question “how to debug a no-code tool”? Especially AppSheet is new to me and I would love to see what is going on under the hood. The Chrome Developer tools did not help…
-s

Steve

There are no debugging tools. There’s an audit log that captures errors that occur server-side, but that’s it. Otherwise, it’s up to you to find ways to expose exceptions. It’s very much a trial-and-error process. AppSheet can be very frustrating to someone coming from a harder-core technical background.

Sven1

@Suvrutt_Gurjar: would it help if I shared my project so you can have a direct look? Maybe there is a bug in your AppSheet framework?

BR
-s

Sven1

This is insane: I just did a couple of changes like copying and pasting stuff around, testing new actions, and whilst reverting everything back to original, ie. @Suvrutt_Gurjar examples, it worked… Thanks for your help on this!

Suvrutt_Gurjar

That is great @Sven. Nice to know it works for you the way you want.

I was about to post that you may either share your app with test data with me or I can share my test app with you.

It will be great if you post the final expressions you used in that said action.

BTW I really appreciate the methodical way in which you had posted your requirement. Also I see that you have joined the community recently and you have mentioned that you are kind of new to AppSheet. However your AppSheet awareness is impressive , going by the type of actions you are working on.

All the best with your app.

Suvrutt_Gurjar

One thing I wanted to mention that it seems that you have used RowNumber as a key in your tables. If you have multi user app , then one need to refrain from using row numbers as key column.

Please read AppSheet recommendations of record keys.

Sven1

Hey @Suvrutt_Gurjar!

the final expression that worked was the one you gave me.
SELECT(MeineAnmeldungen[_RowNumber], [AusfahrtId]=[_THISROW].[Row ID])
Thanks again for your dedication, that is something I only know from Apigee (now Google)

After deploying the App I actually realised that the pricing will be a showstopper: whilst evaluating AppSheet for my local Mountainbiking association (with potentially 500 users - no real budget) I did not notice it would cost up to 2.500$/month (5$/user). For a non-profit organisation it is just too expensive.

All the best!
Sven

Suvrutt_Gurjar

@Sven,

Thank you for the update.

You are correct that AppSheet community as a whole is one of the most responsive and helpful community.

regarding your plan requirement, I believe you may wish to reach out to AppSheet sales team at sales@appsheet.com.

Just one point I may mention here is that when you say there are 500 users, you may wish to know the way AppSheet regards a user for pricing purpose. Please take a look at the definition of “active user” at https://solutions.appsheet.com/pricing just in case you have not.

akon

thanks for the great knowledge sharing here will implement the knowledge shared here.