HELP - BOT - ACTION for deleting a row

[jaichith]

Dear friends,

I need a help from this esteemed community for creation of bot and action for it.

I have table called Users and it has a [email id] as one of the column and [user name] is the key column for users table . I have another table called filter dashboard which has [email id] as key.

Note: [Filter id] in the filter dashboard table has email address. so I have mentioned in the above para as [Email]

Need : When a user is deleted from the user table, email id and its entire row should be deleted from filter dashboard table also.

I don't know how  to create an appropriate  action and to configure the bot.

Things which I have done and that didn't work,

Referenced row : select(filter dashboard[FILTER ID],not(in([FILTER ID],users[EMAIL])))

@AleksiAlkio , @Marc_Dillon @Suvrutt_Gurjar @WillowMobileSys @Aurelien @MultiTech 

[Suvrutt_Gurjar · Accepted solution]

Please try below.

1. Please hide the 'Delete" system action by enabling "Do not display" option on the Users table. Also make this action's "Needs confirmation setting off.

2. Please create a reference action (type : execute an action on a set of rows) called say "Filtered Dashboard User Delete" on the Users table with settings as 

      A. For a record of this table "Users"

      B. Referenced Table" "Filtered Dashboard"

      C. Referenced rows expression : SELECT(Filtered Dashboard[Filter ID], [Filter ID]=[_THISROW].[Email ID])

       D. Referenced action : 'Delete" ( Select "Delete" system action of the "Filtered Dashboard" table.

3/ Create a grouped action on the Users table called "Master Delete" with suitable prominence  as you want.

       A. In this group action select "Filtered Dashboard User Delete" created in step 2 above as first action.

       B. Select the "Delete" system action on the table Users as second group action.

4. Now when the user executes the action "Master Delete" created in step 4, first the record from the "Filtered Dashboard" will delete, followed by the record from the Users table.

[AleksiAlkio · Accepted solution]

Would it be just easier to use the Bot as in your original post? Then you can set the trigger as Deletes_Only. The Referenced rows could have a formula as LIST([Email ID]). That should be enough to find the correct row.

[WillowMobileSys · Accepted solution]

First, you should NOT use User Name as a key column.  Inevitably, the app will enter a duplicate name.  Email is a better choice since it MUST be unique per user.  But email can change, so I prefer a dedicated key column, with randomly generated value - UNIQUEID().

In the Filter table, set the user column as a Ref column (if not already) and then turn on the "Is part of" property - creating a parent/child relationship - then whenever you delete the User, the associated Filter row(s) will AUTOMATICALLY be deleted as well!!  It's that simple!!

[Suvrutt_Gurjar · Accepted solution]

Please try below.

1. Please hide the 'Delete" system action by enabling "Do not display" option on the Users table. Also make this action's "Needs confirmation setting off.

2. Please create a reference action (type : execute an action on a set of rows) called say "Filtered Dashboard User Delete" on the Users table with settings as 

      A. For a record of this table "Users"

      B. Referenced Table" "Filtered Dashboard"

      C. Referenced rows expression : SELECT(Filtered Dashboard[Filter ID], [Filter ID]=[_THISROW].[Email ID])

       D. Referenced action : 'Delete" ( Select "Delete" system action of the "Filtered Dashboard" table.

3/ Create a grouped action on the Users table called "Master Delete" with suitable prominence  as you want.

       A. In this group action select "Filtered Dashboard User Delete" created in step 2 above as first action.

       B. Select the "Delete" system action on the table Users as second group action.

4. Now when the user executes the action "Master Delete" created in step 4, first the record from the "Filtered Dashboard" will delete, followed by the record from the Users table.

[jaichith]

Sir, thanks.... I can understand from your reply... That there is no bot involved. Can we do the same kind of process for adding an user ?

[jaichith]

It is not working sir.

I have done as per your instruction . please guide to achieve this. 

-------------------

[Suvrutt_Gurjar]

---

@jaichith wrote:

Can we do the same kind of process for adding an user ?

---

Can you elaborate what you mean by this? If you mean adding a record to the "Filtered Dasboard" table when a user is added to the User's table, you could try an action type " Add a new row to another table using the values from this row" as an event action in the Users table' s form save  event.

[Suvrutt_Gurjar]

Another way to simplify this could be to have "Filtered Dashboard" record as child record of the User's table with "Is Part of" setting enabled in the reference column. Then whenever the User deletes the User table record , the child 'Filtered Dashboard" record will also be automatically deleted. In that case all these manually constructed actions are not required.

Another way to evaluate the requirement is , is the "Filtered Dashboard" table really required? Could you have those columns of user choices in the Users table itself? Any specific reason you have those two different tables?

[jaichith]

This Filter dashboard table is to generate user filtered PDF. This table helps to extract records from another table and it will be fed to PDF Report generation. 

[Suvrutt_Gurjar]

Okay, but in general it could be same as Users table, right?  Can those choices be not included in the Users table itself? 

[jaichith]

I have several tables. User table, Consignment table, Filter dashboard table. 

Filter dashboard table has the input for the filter criteria.

There is slice for consignment table with row fill condition ,that works Based on the Filter dashboard values( user input). 

Earlier filter dashboard was working commonly for all user that has lead to the malfunction while 2 users use at same time . similar issue posted

So I want to create separate filter id for each user. creation of user is possible but while deleting , it could not be achieved.

User table comes into picture while adding and deleting the rows in the filter dashboard table. 

[AleksiAlkio · Accepted solution]

Would it be just easier to use the Bot as in your original post? Then you can set the trigger as Deletes_Only. The Referenced rows could have a formula as LIST([Email ID]). That should be enough to find the correct row.

[jaichith]

I have doubt ... This expression  List ([EMAIL]) will delete the rows which is not in the users[email] ? 

[jaichith]

Do you have any other method, where bot is not involved like suvrutt sir method?

[AleksiAlkio]

There is nothing wrong with the apporach Suvrutt proposed.

The LIST([Email ID]) reads the value from the record you just deleted and it converts it as a list. Because it only have one value, it finds that row from your other table and then that will be delete as well.

[jaichith]

@Suvrutt_Gurjar , @AleksiAlkio I have tried using both action method and bot with action method. both it did not work. 

in automation manager it shows success ☹️

[Suvrutt_Gurjar]

Please check Audit History log for possible errors.

[jaichith]

Success !! ☹️

[AleksiAlkio]

You said in the beginning "I have table called Users and it has a [email id] as one of the column". You need to use that column with this LIST() that contains the value that is possible to find from the other table's key column. If it's not, the Bot won't do anything as it doesn't find any rows.

[jaichith]

Firstly, I have tried with the Action method insisted by @Suvrutt_Gurjar . I have changed the select expression with List ([Email]). It didn't work. 

Secondly, I have tried the action by attaching it to the bot. Even that also didn't work.

Dear @AleksiAlkio and @Suvrutt_Gurjar  can you please test this method at your end and convey about the viability to me because I have followed the instructions given by both of you but still efforts didn't get success.

[Suvrutt_Gurjar]

Hi @jaichith ,

---

@jaichith wrote:

can you please test this method at your end and con

---

Just to assure you, I always test before suggesting any substantial solution. 🙂 Otherwise those steps cannot be listed so much in detail.

As for Aleksi, please note that he is one of the senior most AppSheet experts and we keep learning many tips from  from him. His solutions are always very practical.

Here are the screenshots o my testing before suggesting you.🙂

Were you able to test the Audit log history as requested?

[jaichith]

now it is working, the bot as well as action did not work because of security filter for the row filter condition said by @Aurelien . which I did not notice. 

Issue was Fixed by following things.

1. removing filter expression

2. created a slice for the filter dashboard view with row filter condition [filter id]= useremail() 

[Suvrutt_Gurjar]

Thank you for the update. Good to know that both the suggestion work with minor tweak you needed to do. You as app creator know such nuances in your app ( in this case security filter, sometimes it can be some column name , type and so on) that we others responding in the community will not know just based on your description.

Anyone asking question may many times need to adjust that fine tuning. 

I will also request you to consider all excellent suggestions by other experts- key column by @WillowMobileSys , "Is Part of "arrangement  by @WillowMobileSys  and I  , just using User table by @MultiTech and I.  Please do study pioneering tip on user based operations in an app, using "Current User' system by @MultiTech 

Of course you need to take the final call based on your app design.

[Suvrutt_Gurjar]

---

@AleksiAlkio wrote:

LIST([Email ID])

---

@jaichith : The reference rows expression proposed by Aleksi is a smarter way of getting the referenced rows. In your case there is only 1-1 relationship between two tables. So an expensive SELECT() is unnecessary, even though it sound a familiar format.

[WillowMobileSys · Accepted solution]

First, you should NOT use User Name as a key column.  Inevitably, the app will enter a duplicate name.  Email is a better choice since it MUST be unique per user.  But email can change, so I prefer a dedicated key column, with randomly generated value - UNIQUEID().

In the Filter table, set the user column as a Ref column (if not already) and then turn on the "Is part of" property - creating a parent/child relationship - then whenever you delete the User, the associated Filter row(s) will AUTOMATICALLY be deleted as well!!  It's that simple!!

[MultiTech · Accepted solution]

---

@Suvrutt_Gurjar wrote:

Another way to evaluate the requirement is , is the "Filtered Dashboard" table really required? Could you have those columns of user choices in the Users table itself? Any specific reason you have those two different tables?

---

This is the method that I prefer for creating this sort of filtering system, which I call "Enhanced Dashboards."

There might be several reason why you'd want the filters to be separate, but generally speaking it's going to be easier (in the sense of complexity reduction) to just include these filter fields inside your root user table. 

• This way when a user is modifying their filter values, they're modifying their user record - and you won't have the possibility of overwrites, as each user is editing their own record.  
• Plus you have the reduction of not needing to create another slice, apart from your Current User slice (which you might already have), further reducing complexity. 
• When you need a value from the filter, you just call the current user slice: Index(Current_User[User_Filter_Customer], 1)

______________________________________________________________________________________

You can learn more here.

[jaichith]

Still I could not understand your point due to my inability. 

the app which I built with reference for several youtube videos of you @MultiTech , joe tayatac, in simple it is blend of youtube videos 😂. 

Soon I will seek your help in File storing, retrieval (PDF ) for completion this app. Reference Video 1

[jaichith]

Summary of this Thread

Case 1 

Problem : When a user is deleted from the user table, email id and its entire row should be deleted from filter dashboard table also.

Techniques used :

 Action : type : execute an action on a set of rows 

This technique avoid bot usage and makes the process faster too.

&

Things to know :

(i) For a record of this table - it is the table where entries are made by the user - user table

(ii) Do this - select type of action needed

(iii) Referenced Table - It is the targeted table where impacts should be made. - Filter dashboard table

(iv) Referenced Rows - Rows that should be targeted in referenced table and it is referred by an expression (usually from user input table - for a record of this table)

(v) Referenced Action: Actions should be targeted in referenced rows

Procedure : 

1. Please hide the 'Delete" system action by enabling "Do not display" option on the Users table. Also make this action's "Needs confirmation setting off.

2. Please create a reference action (type : execute an action on a set of rows) called say "Filtered Dashboard User Delete" on the Users table with settings as 

      A. For a record of this table "Users"

      B. Referenced Table" "Filtered Dashboard"

      C. Referenced rows expression : SELECT(Filtered Dashboard[Filter ID], [Filter ID]=[_THISROW].[Email ID]) . Note: this expression could be simplified as LIST([EMAIL]) since action knows which row it belongs to . ( It was suggested by @AleksiAlkio )

       D. Referenced action : 'Delete" ( Select "Delete" system action of the "Filtered Dashboard" table.

3/ Create a grouped action on the Users table called "Master Delete" with suitable prominence  as you want.

       A. In this group action select "Filtered Dashboard User Delete" created in step 2 above as first action.

       B. Select the "Delete" system action on the table Users as second group action.

4. Now when the user executes the action "Master Delete" created in step 4, first the record from the "Filtered Dashboard" will delete, followed by the record from the Users table.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Case 2 

Requirement : Adding a record from one table to another table (adding new row ) automatically  without using a bot

Procedure:

Table 1 : Users ( Where entries are made by the user)

Table 2 : Filter Dashboard (Targeted table where records should be added automatically )

Brief : Adding a record to the "Filtered Dashboard" table when a user is added to the User's table

1. create action with type " Add a new row to another table using the values from this row"

and set For a record of this table : Users

2. Do this : " Add a new row to another table using the values from this row"

3. Referenced table : Filter Dashboard

4. Set Column Values 

5. save it

6. Go to the users table form view UX , at last find BEHAVIOUR --> EVENT ACTIONS (form saved) ---> finally select the appropriate new action created for creating a new record. 

____________________________________________________________________________________________

New Learning and things to noted  :

1. Avoid using select () function and simplify expression. ( suggested by @Suvrutt_Gurjar and @AleksiAlkio) 

2. Check for security filters ( that creates nuisance and makes the action to get failed)

3. Better to use proper nomenclature 

4. Avoid using bots

5. "is PART OF " this deletes the child records when parent record is deleted (suggested by @WillowMobileSys )

6. Enhanced Dashboard suggested by @MultiTech 

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

I thank Mr. @Suvrutt_Gurjar  who was with me throughout the issue and

I thank Mr. @AleksiAlkio who was a guiding me in right path  .

Special thanks to  @WillowMobileSys& @MultiTech   who have spent their valuable time and making my goal to get accomplished

With Regards

Jaichith J

[MultiTech]

---

@jaichith wrote:

      C. Referenced rows expression : SELECT(Filtered Dashboard[Filter ID], [Filter ID]=[_THISROW].[Email ID]) . Note: this expression could be simplified as LIST([EMAIL]) since action knows which row it belongs to . ( It was suggested by @AleksiAlkio )

---

This summary is great!  But why brute force the list of filter records?

If you made one of the columns in your filter table a ref connection to the User table (I think this would be the [Filter ID] column, as it seems like this contains the UserID), this will give you your Related Filters automatically.

I believe this was mentioned in there somewhere; this would make much of this "easier" on the system, computationally.  (But honestly we're talking such small table sizes, it's pretty moot.)

_________________________________________________________________________________

@jaichith Cheers mate. Long discussions like this are fantastic for others to learn from.

[jaichith]

Thanks matt, if I change the key column to [EMAIL] in the user table other tables will get errors because I have made the setup in such a way. Soon i will incorporate them as per your advice. 

[MultiTech]

---

@jaichith wrote:

if I change the key column to [EMAIL] in the user table

---

I was saying in your Filter table, ref connect this to your user table. (Not to change your Users table)

• When you do this the system creates a reverse reference [Related Filters] automatically on the user table.
• This VC will contain the list of filter records specific to the user, but it's super efficient using references.
• Then you won't need to use a select() or filter() statement to get the filter record for the user
  • [Related Filters] is what you would use instead.