Line items from different tables on same report.

[Mike Tubman]

Hello All,

Hopefully I can explain this without confusing anyone too much. First off, I am running FM Pro 2023 on the Filemaker Cloud.

I have a solution that is tracking jobs in the field. I have a job table with a job line item table that includes the material used for the job. those line items are entered through a portal on the job layout.

Then I have a time sheet table with line items for an individual employee for a specified day. These are entered daily also through a portal. There can be multiple jobs per time sheet. Each time sheet is per employee per day

For tracking, this works great, I have a relationship between the time sheet line items and the job and I placed a view only portal on the job layout to view the labor line items and I have some calcs in the jobs table to add up all the hours and money. 

The issue I have is that I need to create an invoice with both the material line items AND the labor line items. Currently I have a "Submit" button on the time sheet layout so when a time sheet is entered for a specific employee, we hit that button which runs a script that goes through the line items of the time sheet and basically duplicates those line items into the job line item table. That way when we go to invoice we can do GTRR to the job line item table sort them so the labor is first and just print out the invoice. I know of no way to go to a layout that will show line items from 2 separate tables.

It seems wrong to me that I have to duplicate line items and that there must be some way to do this without having to. I don't want to rewrite the time sheet process to enter per job, as the dailies come in by employee, so there would too much back and forth if multiple employees worked on the same job. I was looking into going to the related labor and creating a PDF, then going to the related material and appending the PDF but I don't think I can do that without ALWAYS having separate pages for labor and material. Some of our jobs are very small and only have a couple of material items and one labor line so that will not work. Perhaps using a temporary staging table to add the line items, which is still duplicating but at least that way I can delete them afterwards.

If I haven't lost you yet, any suggestions would be greatly appreciated. 

Thanks

Mike.

[comment]

3 hours ago, mike Tubman said:

a script that goes through the line items of the time sheet and basically duplicates those line items into the job line item table.

If they need to end up in the job line items table, why not create them there to begin with?

 

[Mike Tubman]

So instead of having a separate line item table for the time sheets, use the same one for time sheets and jobs?  Hmm, I don't see why that wouldn't work. Just move the existing data, change the portal and enter directly.

Thanks that is a great idea!

[Søren Dyhr]

On 3/14/2024 at 12:05 AM, Mike Tubman said:

So instead of having a separate line item table for the time sheets, use the same one for time sheets and jobs?

Here's another approach to the matter: https://filemakerhacks.com/2020/02/19/virtual-portal/

--sd