Delete row(s) containing a value in another table from an action

I am looking to have an action delete a row or rows in another table that match criteria using a data from the originating row.

For example (for a bookmarking process)

Table A - large data-set of read only data (Where the action will be invoked from a button in detail form)
has a column called โ€œPermitNumโ€

Table B (where bookmarking information is stored)
has a column called โ€œPermitNumโ€ that contains values that may match โ€œPermitNumโ€ from the original table. As well as the email address of the current user (who wishes to bookmark)

I would like to have an action button on a detail view in Table A that will delete the row or all rows in Table B where PermitNum = PermitNum (to remove the bookmark)

For context, the bookmarks are displayed in table A via a format rule that looks for matches between the two tables (IN([PermitNum],Bookmarks[PermitNum]))

Any help would be great. I have been trying to accomplish with an action that executes an action on a set of rows but cant get any of the โ€œReferenced Rowsโ€ criteria to work.

Solved Solved
1 15 3,240
1 ACCEPTED SOLUTION

Steve
Participant V

For Referenced rows, try:

FILTER(
  "Table B",
  ([_THISROW].[PermitNum] = [PermitNum])
)

View solution in original post

15 REPLIES 15

Steve
Participant V

For Referenced rows, try:

FILTER(
  "Table B",
  ([_THISROW].[PermitNum] = [PermitNum])
)

Damn! What great support.

Worked perfectly.

I really envy those who can think in appsheet โ€œexpressionโ€ like your first born language.

Thank you so much.

Thanks, Steve, for this helpful answer. I had a similar issue to the one stated here and I have found a critical detail that might interest others:

There is a tremendous difference between 

([_THISROW].[PermitNum] = [PermitNum])

and 

([_THISROW].[PermitNum] = Table B[PermitNum])

using the full name (table name + field name) is actually a default when you use the UI to create the filter (click "insert" next to the field name). The result, however is a list of references to all the rows in Table B that have a matching row in Table A (with all kinds of PermitNum). Basically, an action following this filter performs a bulk action that is not limited to [_THISROW].[PermitNum] but any Table A[PermitNum] that has a match in Table B.

Do you know where I can find more about this?

Edit:

This video says it perfectly:

User[first_name]
"This will give us every first_name in the table User"
 
Now, my issue makes perfect sense (I used a list instead of a single value) but where can I find this in the official docs?

Thanks for this great effort. I have a problem with a similar scenario, but I want to add another condition so that cell [ID] of table A = cell [ID Contract] of table B, and cell [Cancellation Date] of table Q < cell [Due Date] of table B

Steve
Participant V

โ€œthe row or all rowsโ€ = FILTER(...)

โ€œin Table Bโ€ = "Table B"

โ€œwhere PermitNum = PermitNumโ€ = ([_THISROW].[PermitNum] = [PermitNum])

I am trying to accomplish a similar process. I have three main tables: Attendees Table, Event Table and Attendance Log table. The goal is to have a list of events that users can choose that they want to sign up for or drop themselves from the event.

The attendance log table has references to the attendees table and event table and looks like this:

The event table looks like this:

I have created an attend event action that does the following:

When I tried implementing the suggestion of

FILTER(
โ€œTable Bโ€,
([_THISROW].[PermitNum] = [PermitNum])
)

It deleted all of the attendance log. Any suggestions on what I can do to implement this drop event action?

Please post screenshots of the drop action configuration and of the complete Referenced rows expression.

My apologies, here is the screenshot from the expression:

Try replacing your expression with this:

IFS(
  ISNOTLANK([eventsid]),
    FILTER(
      "Attendance Log",
      ([_THISROW].[eventsid] = [eventsid])
    )
)

This expression still dropped all of the attendance log. I feel like I need to have some unique identifier that concatenates User + Eventsid for the expression to know to look at a specific eventsid for a specific user. Right now it seems to drop all of them because the eventsid matches everytime between the event table and the attendance log table, because it is a reference column and therefore will always match.

This Drop Action is presumably being triggered from the particular Event record?

I think use this as your referenced rows expression:

SELECT(
[Related Attendance Logs][_RowNumber] ,
[Email] = USEREMAIL()
)

By the way, I, and many others, would highly recommend setting up a Key column populated by Initial value of UNIQUEID(), instead of using _RowNumber as a Tableโ€™s Key.

Thanks for the suggestion on the referenced rows expression, I believe that worked. I also appreciate the advice on the key column, however when I switch the key to my attendanceid column which has the uniqueid() expression I get this message:

Should I have a formula in the google sheet in that column that has uniqueid?

Thanks,

Initial value, not app formula

Got it, thank you for the clarification! Really appreciate your help in figuring this out.

Top Labels in this Space