Setting Key Field/Formula Field as Key Field Issues
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
------------------------------
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
------------------------------
