New to implementing anything but the simplest “send email” automation, so hoping I did my initial design right.
We’re trying to build a log for tracking if timecards from various crews (construction) have been submitted. Table A has a list of all the shifts that we’ve scheduled coupled with the day the shift starts (IE we can have a 12/14/21 Day Shift, Swing Shift, Or Night Shift). This table would be filled in the office (calendar view) and we would assign a foreman to each shift. The Primary field (right now) is a formula combining the date and shift, so a typical record looks like this:
Date-Shift (Formula) ||| Date (Date Field) ||| Shift (Single Select Field) ||| Foreman Name (Linked Record to a Foreman Table)
Then Table B is for the foreman to fill out via a form view. They get a date picker field, a shift select field, a foreman linked record (just like Table A) and then an attachment field to upload their timecard file. Would look like this
Date-Shift (Formula) ||| Date (Date Field) ||| Shift (Single Select Field) ||| Foreman Name (Linked Record to a Foreman Table) ||| Timecard Upload (Attachment Field)
I’m encountering two issues on my path to linking these. What the workflow should be is in the office, we will out the schedule for the week(s) ahead with what shifts will be worked on what dates and what foreman will be assigned to those shifts. Then when a foreman completes their shift, they go to their form view, select the shift start date, the shift they worked, who they are (foreman) and attach their timecard.
What I want the automation to do is when that foreman submits a new record with their timecard attached, the new record in Table B has a primary field of Date-Shift that would hypothetically also match a the primary field of a record in Table A (also Date-Shift). This will let us know if all the timecards have been submitted for each shift (and automatically send an email reminding a foreman if say it’s 24 hours past due for a shift they were assigned).
What I am finding is this:
A) if I go to “find record” in Table A based on the shift (can be a dynamic condition from table B) and the date (cannot be a dynamic condition it seems), then I am stuck because what I need is to base the date we are looking for in Table A off the date of the new record in Table B. This doesn’t seem possible because the condition “Where Date is ____” does not accept a dynamic variable from the new record in Table B.
B) if I go to “find record” in Table A based on the primary fields (which should match) by grabbing the Primary field of the new record in Table B (lets say 12-14-2021-Swing) and trying to find a record in Table A with the same Primary field (again 12-14-2021-Swing) - when testing it returns 0 matching results (but says that the test ran successful).
Haven’t even gotten to doing the linking properly yet, but I guess is there something I am doing wrong where I should be able to have a dynamic variable for the date field condition, and/or is there something I’m doing wrong/could change when trying to go through the primary fields that are created via formula?
