Setting an initial value in a ref column

[VLMark37]

In my app, I want a reference table to default a value from another table and also be editable.

In my "Jobs" table, you can choose a customer from a "Customers" table which has an assigned Driver from the "Team" table.

Each table has a ref column link in the "Jobs" table.

I have tried using ANY(reftable[refcolumn]) formula in "Assigned Driver"; however, it will add the driver into the drop down field again, i.e. not tying to the driver's list of jobs as a related job in a virtual column.

In order to get the job to show up, you have to select the specific driver again from add form. 

What I would like is for the assigned driver to default from the customer list but have the ability to reassign a driver for the specific job without overriding the assigned driver in the customers table so that the job will show up in the related job for specific Driver related job in the Team Table.

In screen shots below, the arrows indicate where the data is entered or viewed in relation to the Jobs table.  The "Driver ID" is reference from the "Customers" table and does not auto populate.  In my "Customers" table, "Driver ID" is the key to my "Team" table.  The "Reassign Driver" is referenced to the "Team" table.  I know there is a bit of redundancy.  Again, I am trying to default the driver assigned to the customer and reassigned driver only related to the job.

An If then formula may also work; however; I am not sure where to apply.  

Any help would be appreciated.

Thanks,

VLMarks37 

[WillowMobileSys · Accepted solution]

---

@VLMark37 wrote:

What I would like is for the assigned driver to default from the customer list but have the ability to reassign a driver for the specific job without overriding the assigned driver in the customers table

---

in your Jobs table, you already have a Ref column to the Customer in the column  named "Cus ID".  You can the dereference pattern for the Assigned Driver column just like you did for the "Customer" column to get the name

In the "Initial Value" property of the "Assigned Driver" column of the Jobs table, use an expression like this:

[Cus ID].[Assigned Driver]

Remove the LOOKUP() function from the "App Formula".  The expression above will set the value from the Customer table as a starting value which a user can then change IF you have set a proper list of possible choices form the column dropdown.

NOTE:  If the [Address] column also comes from the  Customer table, you can use the same "dot" notation idea above to get the Customers address.

[VLMark37]

This works; however, I still need for the job to show up when looking at the reassigned driver in the teams table as a virtual column as a related job.   If I try to make the "Assigned Driver" column a reference to the "team table" with the [Cus ID].[Assigned Driver], it has been adding the driver again in the drop down list with a an error triangle.  The error being it the default is not tied to the driver key in my team table.  Basically, I have to enter the driver in every time regardless if they are assigned or not in order for the job to tie to the driver in my teams table.  

I was hoping to find a default for the assigned driver so I don't have to enter them each time.

Any suggestions?

[WillowMobileSys]

---

@VLMark37 wrote:

it has been adding the driver again in the drop down list with a an error triangle.  The error being it the default is not tied to the driver key in my team table.

---

The yellow triangle simply means that the inserted value is not valid value to be entered based on the column dropdown list configuration.  

If you have inserted some expression that returns a list in the Valid_If property, make sure that list contains all of the possible values including the value you are trying to insert.  It would be helpful to show us this column configuration and where you are seeing a problem.

[VLMark37]

I went back and examined the relationships and formulas based on your suggestion and it worked!!

Thanks!

[VLMark37]

I would be happy to show this; however, you'll have to excuse my novice ignorance.  Is this what you're looking for?