Manipulating List-User information for calculations

Question

I have a list-user field called Tech Recipient, it's for a maintenance company build. Tech stands for technician.

Per job, users are entered into this field to show who did a job. Also on this form, we capture total hours for the job. What I am trying to do is create a summary report that tells me how many hours each technician did (we billed for) per week, based the Tech Recipient field vs. how many hours the job took.

For example, "John Doe" is a tech, and "Adam Smith" is a tech. They both worked on a job, and were listed under the Tech Recipient (list-user) field. Both are users on the application. Further down in the form there is a numerical field reading that job took 8 hours.

I have created a new field (Formula-numeric) to try and figure this out. All formula attempts have failed. Any suggestions?

------------------------------
Jeff Nixon
------------------------------

MarkShnierYou · Answer

It's a problem you're having is that you have taken the shortcut of using a list use your field for the technicians. It would be far far easier to summarize the data if you list the technicians in the child table. That way they would be independent records and they would inherit the time of the job down to their records. That it would be a simple job to summarize the time per technician.

So my first suggestion is to change your entry method to have a trial table on the project of assigned technicians. This would be a Many to Many join table where one job has many assign technicians and where one technician is assigned to many jobs.

(having said that, there is new functionality coming "soon" (like this summer) with Formula Queries which is very mind blowing to us long time developers, so we have to re-think how we say you "have to " structure your app.

If however you are completely married to continuing to have the convenience of a list user field for the assigned technicians, then you will need to set up an automation or a pipeline to create and maintain those child tables tables automatically. We're building those automation or pipelines, I would trigger whenever the project records are being added or modified and when that field changes and the first step would be to delete all the assigned technicians and then rebuild them.

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------

Sign up

Login to the community