Change value of a column in a table when form is submitted in another table

[Cristian_Maria]

I am creating a basic CRM for internal use.

I have a table “organizations”, containing the columns [organization name], [stage], plus others which are not important

Than I have a table “projects”, containing the columns [organization name], plus others plus others which are not important

The column [stage] in table “organizations” is displayed as dropdown with fixed predefined values:
Cold Lead
Contacted
Interested
Call Scheduled
Pilot Project
Project

What I would like is that whenever I submit (the form view) of the table “projects”, it should, of course, first of all add the new row in the table, as usual, but also change the value of the column [stage] in the “organizations” table to “Project”, from whatever it was before.

The form view of table “projects” will have the [organization name] displayed as dropdown, and the choices will be the values of the column [organization name] from the table “organizations”.

NOTES:

• I know how to dynamically populate dropdowns with data from other columns
• The column [organization name] in table “projects” will be a REF type, (displayed as dropdown), and will be refferencing [organization anme] in “organizations”; this raises an additional question, whether this column should also be marked as “is part of” or not…

Looking forward to your answers, and please let me know if you need additional details.

[Marc_Dillon]

1. Make an action for the Organization table that changes the column value.
2. Make an action for the Project table that is a “reference action”, its called “data: execute an action on a series of rows” in the editor. Use the [organization name] column as the referenced rows.
3. Set the action to run upon saving of the form, find that option under Behavior section in UX.

[Cristian_Maria]

@Marc_Dillon

1. Completed, but what purpose does it have? What is the “trigger”?
2. I can’t choose the [organization name] column as referenced rows; error displayed: “The expression is valid but its result type ‘Name’ is not one of the expected types: List”

From what I know, a list type is only good for virtual columns, so of course my [organization name] column in “organizations” table is a name type

If there’s something fundamentally wrong with my logic, I apologize, I’m quite new to appsheet.

[Marc_Dillon]

Oh right. We’ll just convert organization name to a single item list. Use LIST([organization name]) as the Referenced Rows. As far as the trigger for #1, you’ll select the action in the specification of action #2.

Here’s a sample
https://www.appsheet.com/samples/This-app-shows-how-to-use-reference-actions?appGuidString=e76d2e73-...

[Cristian_Maria]

@Marc_Dillon

It works (with a slight adjustment), thank you so much!

For other users who might be interested in this:

Use LIST([organization name]) as the Referenced Rows.

I DIDN"T have to, because I was actually making a mistake and writing just “[organization name] in the expression field, instead of organizations[organization name]”

[Marc_Dillon]

organizations[organization name] is going to give a list of ALL records in the organization table, and thus the action will run on ALL of them. I don’t think this is what you want, I think you want LIST([organization name]). That will return the single organization record referenced by the project record that you are modifying.

[Cristian_Maria]

you are absolutely right! I’ve changed accordingly, ( after having to edit my entire sample data :)) ), now all looks good!

[Cristian_Maria]

@Marc_Dillon

To make this even more useful, could I also change the value of [stage] to something else if the project created earlier is deleted?

[Marc_Dillon · Accepted solution]

Sure you can. You can create a workflow that runs only upon Deletes, which triggers the actions to run. Or you can replace the system-generated Delete action with one that is a grouped action (although keep in mind there may be other ways that a record could get deleted, and thus wouldn’t trigger everything you want).

[Cristian_Maria]

Well, I’ve tried it, but I need an action to have effects on table “organizations”, but be “triggered” when a row is deleted in table “projects”.

When I try creating the workflow, I can only add actions created under the target data table, which, in my case, is “projects”. However, the action I need is created under table “organizations”, so I can’t find it & add it to the workflow…

[Cristian_Maria]

Solved, I was able to find the actions after saving the changes to the app. Thanks again for your help !

[Fabio_Nabuco_Co]

Dear all;

I’m having the same difficulty Cristian had.
One table is called Expedição and the other is Pedido. Both of them have a common named column, ( Expedido).

The table [Expedição] pulls the ref of the orders (Número do Pedido) from the table [Pedidos], and creates a dispatch form, with the amount of each item dispatched, the time it was dispatched, amongst other informations.

What I’m trying to do is to reference the (Expedido) column of the [Expedição] table to the (Expedido) column of the [Pedido] table.

What I’ve got so far:

I have created an action, in which it changes the value of the column (Expedido) in the [Pedido] table, but I’m not sure how to create the reference between the sales order ( Número do Pedido), which is the reference number for the sales order.

Thank you all for the support.

Regards,

Fábio Nabuco Correia