I have a app with customers who have can have multiple service locations. We enter a log of services to them at the various locations. The service location repair table has columns for customer and service location as well as service details. I like to be able to make needed changes to a customer’s name or their service location name if needed so I keep a uniqueid() in those tables to make this possible. However the UNIQUEID is the value returned to the service location repair table because it is the key. Is there a way to return the customer name and service location name to the service location repair table while retaining the benefits of a UNIQUEID key? I need the service location repair table to be the “master list” or repair records.
If you need to use a Ref field, I’m afraid that’s not possible.
I’m still learning. So what is a suitable way to have a single master list (table?) of all services to a customer that records the customer name, service location name. Is there a set up that accomplishes this?
So assuming the hierarchy of your tables is set out like this
Customer Table
|
Service Location Table
|
Repair Table
The Repair table will need to store the keys from both higher tables in
Repair[CustomerID]
Repair[ServiceLocationID]
How you get it to write them depends on wether you give your users a single repair form to fill in or they go Customer->Service Location->Create Repair form
But either way, in the repair table simply create these two as virtual columns with the following formulas
Repair[Customer Name]
Any(Select(Customer[Name],[CustomerID]=[_ThisRow].[CustomerID]))
ServiceLocation[Service Location Name]
Any(Select(ServiceLocation[Name],[ServiceLocationID]=[_ThisRow].[ServiceLocationID]))
So any changes to the Customer or Service Location tables will automatically update the names on any Repair record.
You might also have to use a slice to jiggle the column order around
Hope this helps
1 Like
Thank you for that detail. I will see if I can get this to work. Thanks again.
1 Like
@1minManager and @Scott_Hall, I may be missing something, but I think the virtual column expressions would be very much simpler:
CustomerName would be [CustomerId].[Name]
ServiceLocationName would be [ServiceLocationId].[Name]
We call these de-reference expressions.
1 Like
Hi @praveen
Given a table hierarchy of:
Customer Table
|
Service Location Table
|
Repair Table
And assuming you’re in the Repair table. Then [ServiceLocation].[Name] should work. But my understanding was that [Customer].[Name] wouldn’t as it was going up 2 table levels
Have had trouble getting these expressions to work but still working on it. Will report back on final results so others have a reference. Thank you for your help.
I suspect @praveen’s suggestion is closer to what you need.
You cannot directly dereference a dereference to navigate multiple table levels - but you can use virtual columns to work around that. See the “double dereference” section. I keep this handy because I always forget the correct way to do it! @Scott_Hall - maybe this information can help…
I don’t think @1minManager’s assumption that service location is a child of customer is accurate.
1 Like
Ah… always a challenge to fully understand others app structures…
3 Likes
so when creating a virtual column in the repair table what should the column type be?
Was not able to accomplish my original goal but was able to accomplish a workable solution. My key combines UNIQUEID() & " - " & [Name] to create a key that has the customer’s name as originally created. This way I can at least have a key that identifies the customer even if a slight adjustment or spelling error is made when the customer is first created. Not the cleanest but accomplishes my goal more or less.