Skip to main content
Question

Setting Key Field/Formula Field as Key Field Issues

  • October 25, 2021
  • 0 replies
  • 163 views
Hi All,

I've got a table that's used for people to add their schedule/availability to. At the moment it's not related to anything, it's just used to show me people's schedules. I'm trying to enhance it by relating a child table to it so that when assignments are given I can see on the parent table how much availability someone has left as well as their daily schedule in a report. Each record in the scheduler table usually represents someone's availability for one whole day. However, there are a couple people that have availability in the early morning for a couple hours, and then not again until later that night. Those people add two records per day at the moment.

There are a few things stopping me from successfully relating the scheduler table to the assignments table. The first was what field I would use in the scheduler table (parent table) as the key field. Assignments are made on the child record via a dropdown of people's names. In order to make sure an assignment is counting towards the right day's availability, my thought was to create a formula field with the person's name + the date of the assignment and then use a webhook to put that text into the related field. I tried this without the webhook and got an error when trying to create new summary fields on the parent table that said I couldn't use a formula field as the reference field. This is what the reference field would look like after the webhook: John Smith+10-25-2021. That part works, but that means I then have to have that Name+Date field as the key field in the scheduler table. A concatenation like that is only possible with a formula field, which can't be set as a key field. Another webhook could send that formulated text into a basic text field, but that only happens once a record is added to the scheduler table, and QB won't allow you to create a new record without the key field having a value.

Thoughts? Is there an easier way to do this? At the end of all this I also have to find a way to make this work for the people that work two blocks in one day.

Thanks,

------------------------------
Daniel Johnson
------------------------------
This topic has been closed for replies.

Forum|alt.badge.img+14
You could use a pipeline to create the entries in your parent table. Key Field is a text field, and every time an assignment is created, a Pipeline searches to see if that version of name and date exists. If not, create a record in the parent table with that specific string in a manually entered text field.

The child table you should be able to use the formula field in the relationship. It only fails if part of the formula is from a lookup field. If that is the case, you also make a manually entered field on the child table called 'Name and Date Manual Field'. Create a dynamic rule that upon saving the record, copies the Formula field to the manual field.

But also, I'm not quite understanding why you can't just set all the appointments in your child table, then just create a report that you filter by date or user to see the schedules?

------------------------------
Michael Tamoush
------------------------------
Terms & ConditionsAccessibility statement