Column values for related tables

Andrea2
Participant IV

I have 2 tables that are related by Referencing the “Dealership Name” Table 2 is a form that needs to be completed when the Dealership is visited. I need the column ‘Visited’ In table 1 to add the value "Yes’ to the correct dealership when the data is input in column “Visit Complete” in table 2.
Is there a way to do this?

0 15 777
15 REPLIES 15

AlexM
Participant V

Create an action to set that column to Yes.
Create a change data Workflow To trigger that action when the other column is not blank.

Thanks Alex. But how do I make sure it for the correct dealer. Should there not be some kind of lookup formula?

Table “Dealer” consists of a list of clients. Table “Visit 1” is referenced to table “Dealers” by column [Dealership Name] Table “Visit 1” is a form that has to be completed for the visit to the client. Once marked visited when completing the form I want it to reflect in the table “Dealer” that that client has been visited. There is a column for [Visit complete] in both the tables Dealer and Visit 1. The answer you gave me is not working Alex, perhaps because it does not specify something?

Got it!

Visit complete in Dealers table

IF(COUNT[Related Visits]>0, “Visited”, “”)

I get this message when adding this formula in Visit complete in dealers. Unable to find table ‘COUNT’, did you mean ‘Route’?

If(count([related visits])>0,”visited”,””)

Formula for column visited in dealer table

To get the number of visits you just put

Count([related visits])

And this will bring you back how many visits are related to that dealer.

You can either use this instead of just knowing if he was visited or just add a virtual column with the formula above

Thanks Alex but I don’t want to count the number of visits. I want the user to be able to see in the “Dealers” table whether the specific dealer has been visited. When the physical visit is done there is info to complete which is why I have an action that appears for visit 1 which takes them to the form. Once the visit is marked complete at the end of that form it needs to appear visited in the Dealer table as that is the database overview. There is a format rule attached to visited in the dealers table to change the dealer name to green when visited.

It’s fine. The use this

If(count([related visits])>0,”visited”,””)

Initially I have missed some brackets.
Sorry, I’m on my phone.

This one says if the related visits list has more that 0 records that the column value will be visited, otherwise, will be blank

Lynn
Participant V

Hi @Andrea You could try changing the data using a workflow.

Still not doing what I need. I am battling to link the column Visit Complete in Dealers table to Visit complete in Visit 1 table to extract value from column for correct row that matches the dealer name in both tables.

Hi Lynn

I tried this and got an error that stopped the app from working. I am sure there is a simple solution. The problem I think is that the rows don’t correspond. the action to do the form for visit one selects the dealer name from the list in dealers for the form. So in the visits it is not in the same order and not all the dealers are in that sheet. The rep selects to do a visit when they go to see that dealer and that creates the visit 1.

Hi @Andrea I have been away all day today but will post a link to a sample app you can check out and instructions later tonight.

Hi @Andrea Here is the App and documentation

Thank you Lynn

Top Labels in this Space