Advanced Pipeline Logic
Okay via a pipeline, thanks to Chayce Duncan, I was able to implement the below logic. What this does is sends a report to our foreman every day at 6pm, if they have not approved a time entry. I have...
Forum Discussion
I would provide an alternate view to Don's in that if a foreman has 20 unapproved entries you don't want to fire off 20 emails to them and have their inbox full of unapproved requests.
There is a non-pipeline email solution similar though that you have a table of foreman that you query via Pipelines every day at 6pm, if they have unapproved time entries then you send them an email via Pipelines or basic QB notifications and in the body of the email you send them a link to a report in QB that is a filtered report to them as a foreman of their pending approvals. The body of the email will not include the actual entries in table format, but it would alert them with general specifics all the same.
If you're still set on using Pipelines, you can either 1) modify your pipeline and loop through the foremen with outstanding approvals and for each formeman query their approvals and send it similar to how you already are. This method is pretty taxing though in that if you have 30 foremen with pending entries you'd be querying 30 times which is a lot for a simple process. You could instead query for all outstanding entries, and then do a loop of your foreman and instead of re-querying for their specific entries, do the JSON loop that you're doing for the email and add an IF condition to the JSON string that the foreman on the time entry has to be the same foreman that you're currently sending the email to.
------------------------------
Chayce Duncan
------------------------------
I agree with Chayce. More than one email a day about Timecards will make you unpopular with the Foreman. The architecture change is to add Summary Fields in the relationships.
Your Pipeline Search Step is looking for records in the Foreman table where the # of Unapproved Time Cards > 0. Then check the Email box and then uncheck it so it is blank for tomorrow.
Your Notification is part of the Foreman's table but has a Link to the Time Cards Table where you filter on Foreman User.
Now you do not need any JSON at all.
Do it the way Chayce suggested.
------------------------------
Don Larson
------------------------------
Understood on all accounts gentlemen. The main purpose for the pipeline instead of the native QB notifications is the constraint of time. I could not figure out a way to send an email at an exact time. Only daily, weekly, and monthly. Please see the below photo. Unless I am missing something there, and I need these alerts to go out at 6pm EST every day, therefore, I thought I would have to rely on a pipeline for the time management. Currently our report looks like the snippet below labeled figured 2, and we are satisfied with this look if we could keep something similar. Also, Chayce to your point of sending one report and having the foreman filter, this is something we have thought about within our Dev team, however, we have to verify with our in field reps this will be acceptable to them. We dont want anyone to feel, "called out" either.
------------------------------
William Hunter
------------------------------William,
You will need to use a Notification instead of a Subscription. That has a Rich Text Editor that will let you build a pretty good looking email. You can easily spend more time on that then the architecture and Pipeline.
------------------------------
Don Larson
------------------------------Don, but I will have the limitation of time here correct, I will not be able to send a notification at 6 pm everyday, only when some type of trigger occurs, correct?
------------------------------
William Hunter
------------------------------
Furthermore, Don, unfortunately I do not have an architecture set up anything like this. The foreman live at the job level for each job and are simply selected via a singular user field as we would never have more than one main foreman per job. Therefore, it may require much more work to conform to this architecture and then create the functionality steps.
------------------------------
William Hunter
------------------------------- MarkShnier__You
Qrew Legend
William, I'm with Don here. I suggest setting up a table of foreman with the Key field being the userid. Then make a relationship back to your Timecards table based on your userid field. That will join up all your existing record to the foremans in the table and allow you to make a summary field of the # of unapproved Time records.
Then you create a form which lists the foreman and an embedded report of the unapproved time records.
The set up a Scheduled Pipeline to run at 6:00 and Search for Foreman with unapproved time cards. The a for each loop to update say a date/time field to the current date time. this triggering an email notification to the user in the field foreman userid and include a copy of the form which has the embeded report of time cards.
Native solution with no JSON is the way to go here.
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------ William,
Your Pipeline running at 6:00 is what will fire the Notification. A check box on the target table is all that it takes. You only need three steps
- Search table where we have Time Cards summarized
- Set the Check Box for valid records: Unapproved >0
- Clear the Check Box so that tomorrow
I feel your pain about the User field on the Jobs table. I have been asking QB for years to make the Users in an application a dedicated table. I would suggest something like this
This will pay you dividends in the future as you can do all sorts of reporting on the Foreman table about the # of Jobs, On Time Completion and so forth. I learned the hard way not put User fields anywhere except in an Human Resources type table.
Migrating your existing data to this should not be too bad. I would make a custom report on the Jobs table that has the old User field and the one inherited from the Foremans table. Then you sort by the old field and do a Grid Edit to match the values. Then hide the old field and do let anyone have view or edit authority on it.
------------------------------
Don Larson
------------------------------Don and Mark,
Understood. Thank you gentlemen I will see if I can get this logic implemented.
------------------------------
William Hunter
------------------------------
