Associating records between multiple views

[Erik_Meyer]

I have some tables that look like so:

When in the detail view of Project, I need to be able to add a new switch record and associate it with the project. I have added an action that navigates to a view based on a slice of Switch to allow a new record to be added.

…

Here is my problem: Once I press SAVE on the Add Switch view, I need a record created in the ProjectSwitches table to marry this record of switch to the project it is being added to. So I need to add a record to that table with switchID from the new switch and the SO from the Project record that launched the action.

I would appreciate if anyone could give me some guidance on how to make this association.

[TyAlevizos]

Hi Erik,

I think (not 100% sure) you are looking for a button that calls something like this:
LINKTOFORM(“project_switches_Form”, “soid”, [soid])

I just built out your data model and here is an app:
https://www.appsheet.com/samples/Sample-App?appGuidString=41a6f102-54ff-40e7-9243-23e008c274d5

Is this what you are looking for?

[Erik_Meyer]

Thanks @TyAlevizos. That is close to what I am looking for.

I would like the flow to work like this, however:

1. User selects a Project.

2. User Presses “Add Switch” action button. This takes the user to the form used to enter new switch details, as though they had navigated to the Switches view and pressed the + button. (This is what my app currently does)

3. User enters new switch details.

4. User presses “Save” and is returned to the Project details page. At this point a new record is created in projectSwitches table, adding the ProjectID from the current project and the SwitchID from the new switch they just created.

I hope that makes sense.

[TyAlevizos]

Right. Can’t quite do it with the data model, because switch table has no knowledge about projects, due to the bridging table. But you can get a “new” button for free, when you add a switch to a project, you can create new. The UX will elegantly take you back to the project when you are done. I think this is as close you will get, and I recommend the UX because it’s elegant and the way to go, e.g. this screen when adding a switch to a project, then click “new”:

[Erik_Meyer]

TyAlevizos:

switch table has no knowledge about projects, due to the bridging table

The entire point of the bridging table is to make it so that Projects doesn’t need knowledge about Switches and vice versa. The bridging table knows which switch is associated with each project.

In this app there will be thousands of switches and thousands of projects. The app would be considerably less user friendly if the user is required to scroll through 2000 switches to associate one with a project, or vice versa. This should be an automatic operation based on the context of the app. The user should not have to navigate or even know about the underlying structure of the database. The app should know that I am modifying a project by adding a new switch, the user shouldn’t have to manually make that association.

TyAlevizos:

Can’t quite do it with the data model

How would you change the data model to accommodate this requirement? I have shown only a portion of the database structure. The meat of the entire database, and what my app will be interacting with primarily, consists of these “bridging” tables that keep tracks of what “things” are associated with other “things”.

[TyAlevizos]

we can do it. hold pls

[TyAlevizos · Accepted solution]

"How would you change the data model "

ok look at the app again. One new column called “FirstAddedToProjectID” to the bridge table. Then, a new action, and a new workflow.

Copy and customize the app and give it a whirl.

[Bahbus]

Wait. I don’t want to step on any toes @TyAlevizos, but Project should have some reverse references to ProjectSwitches. So he should be able to craft a VC in Project with a dereference to Switch via ProjectSwitches, and then add that new VC to the slice. Right?

[Erik_Meyer]

@TyAlevizos Thanks, I think I see how that is working.

@Bahbus What is a VC? I would like to know more about your ideas for a solution.

[Bahbus]

I’d recommend following Steve’s advice. He probably sees and divines something I wasn’t from your descriptions.

[Steve]

UNTESTED!!! USE AT YOUR OWN RISK!!!

1. From a ProjectSwitch, create a new switchID for a new Switch.

   • Action name: ProjectSwitches::SetNewSwitchID
   • For records of this table: ProjectSwitches
   • Do this: Data: Set the values of some columns in this row
   • Set these columns:
     • switchID: (expression to generate a new switchID)
   • Only if this condition is true: ("UNUSED" = [switchID])

2. From a ProjectSwitch with a switchID, allow the user to create a new Switch.

   • Action name: ProjectSwitches::EditNewSwitch
   • For records of this table: ProjectSwitches
   • Do this: App: go to another view within this app
   • Target: LINKTOFORM("Switch_Form", "switchID", [switchID])
   • Only if this condition is true: ("UNUSED" <> [switchID])

3. From a ProjectSwitch, create a new Switch.

   • Action name: ProjectSwitches::AddNewSwitch
   • For records of this table: ProjectSwitches
   • Do this: Grouped: execute a sequence of actions
   • Actions:
     • ProjectSwitches::SetNewSwitchID
     • ProjectSwitches::EditNewSwitch

4. From a Project, create a new ProjectSwitch if none are available.

   • Action name: Project::AddNewProjectSwitch
   • For records of this table: Project
   • Do this: Data: add a row to another table using values from this row
   • Table to add to: ProjectSwitches
   • Set these columns:
     • SO: [_THISROW].[SO]
     • switchID: "UNUSED"
   • Only if this condition is true:

     ISBLANK(
       FILTER(
         "ProjectSwitches",
         AND(
           ([_THISROW].[SO] = [SO]),
           ("UNUSED" = [switchID])
         )
       )
     )
     

5. From a Project, create a new Switch from an available ProjectSwitch.

   • Action name: Project::AddNewSwitchtoProjectSwitch
   • For records of this table: Project
   • Do this: Data: Execute an action on a set of rows
   • Referenced table: ProjectSwitches
   • Referenced rows:

     FILTER(
       "ProjectSwitches",
       AND(
         ([_THISROW].[SO] = [SO]),
         ("UNUSED" = [switchID])
       )
     )
     

   • Referenced action: ProjectSwitches::AddNewSwitch
   • Only if this condition is true: TRUE

6. From a Project, create a new Switch.

   • Action name: Project::AddNewSwitch
   • For records of this table: ProjectSwitches
   • Do this: Grouped: execute a sequence of actions
   • Actions:
     • Project::AddNewProjectSwitch
     • Project::AddNewSwitchtoProjectSwitch
   • Only if this condition is true: TRUE

[Erik_Meyer]

Both solutions are greatly appreciated!!

Just so I am clear here:

@TyAlevizos’s solution basically comprises of adding one new field to the Switch table to know which project was creating it, and adding a workflow rule and associated action.

@Steve’s solution calls for using 6 twisty logic, complicated actions.

I am leaning towards @TyAlevizos solution as it seems cleaner, but I would like to know: What are the pros/cons of each of the proposed solutions? Why would I choose one over the other?

[Bahbus]

With the workflow, you’d have to wait for the AppSheet server to finish running before the data it writes would be accessible. With the actions, twisty though the logic may seem, is all directly controlled via the users.

[Erik_Meyer]

Unfortunately, the FK Restraints do not allow @Steve’s solution to work:
.
.
Unable to add/edit/delete row in table ‘ProjectSwitches’. → One or more SQL errors has been encountered:

The insert/update operation does not satisfy the foreign key constraint in your MySQL table.

Original error message from the database: Cannot add or update a child row: a foreign key constraint fails (AppProjects.ProjectSwitches, CONSTRAINT FK_181 FOREIGN KEY (switchID) REFERENCES Switch (switchID))
.
.

@Bahbus the users will only be able to use this while online, I think I am OK with a sync required before proceeding. Is that the only downside to the workflow method?

[Bahbus]

Pretty much yeah. So if the internet goes down, or is just slow, the users may have to wait many seconds (or when internet is back up) for the data to come back available.