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.