A related field has been added to one table but is not being updated from the main table. What needs to be done in order to get the related data updated automatically?

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

I've set up a relationship between a time cards table and the service items table. Service Items to Time Cards is a one to many relationship. IOW, there are many time cards to a service item. Each service item is billed out to clients at a different rate. I'm trying to calculate the client's bill based on time * rate. The problem is that the related item (in the time card table) is not getting updated and therefore, there is no rate to multiply by the time. 

Can anyone help with what I've done wrong or what do I need to do?

Photo of Robin Fisher

Robin Fisher

  • 50 Points

Posted 4 years ago

  • 0
  • 1
Photo of dwhawe

dwhawe, Champion

  • 908 Points 500 badge 2x thumb
In order for the relationship to work, the related item field in the Time Card table that was created when you created the relationship must have a value in it.

In other words, when a record is created in the Time Card table, the field must be populated with the value of the Key Field in the pertinent parent record of the Service Items table.  That is how the parent and child are linked so you then get your data in the summary fields.

So my questions are:

What is the Key Field in the Service Items table?

How is the Service Item parent identified in a Time Card record?
Photo of Robin Fisher

Robin Fisher

  • 50 Points
Record ID# is the key field in the service items - I think it's in the Time Card table as a Related Service Item. However, the service item key field ID is not being updated in the time card table.
Photo of dwhawe

dwhawe, Champion

  • 908 Points 500 badge 2x thumb
The user must select the Related Service Item when creating a Time Card record
Photo of Robin Fisher

Robin Fisher

  • 50 Points
Users do select the item on the form but the related item isn't being updated in the time card table. Can it be done?
Photo of dwhawe

dwhawe, Champion

  • 908 Points 500 badge 2x thumb
How do they select the Service Item?  What type of field?
Photo of Robin Fisher

Robin Fisher

  • 50 Points
When filling out the time card form, employees select the service "Item" which is a text field in the Service Item table.
Photo of dwhawe

dwhawe, Champion

  • 908 Points 500 badge 2x thumb
You may want to consider hiring one of the consultants in the "Partners" list.  They could fix this fairly easily.  It is a little more tough to explain.

First, make a complete back up of your application.  Make sure you include the Record ID fields as they are not typically a default field.

Then, go to the field properties of the Service Item field in your Time Card table and change it to a "Look Up" field.

Once it is a Look Up field, in the Field Properties under Lookup field options | Reference Field choose the Related Service Item and the Value field is the Service Item.  (I am guessing at these names based on your comments above.)