Post Data for Faster Reporting

Discussion created by user28271 on Dec 7, 2017
Latest reply on Dec 9, 2017 by philmodjunk

Usually I ask  questions on this forum, but I thought I would try a discussion this time.


I have a system which compiles information from several tables for a profit and loss report.  The P/L report is the corner stone of my solution which my clients LOVE.  But it is very very slow so I changed my processes which is now very fast but it is posted so it is no longer realtime.  Client can click a button to update, but they forget and send emails that the reports are wrong.


So let's start with the data.

Parent table is JOBS.

Labor links to jobs (and there will be hundred, often thousands of labor lines)

Vendor PO links to jobs

Bills link to jobs (and bills link to billing line and again their will be thousands of billing lines)

Delivery Tickets link to jobs

Fiber Session link to jobs

Expense Reports link to Jobs

Narration contracts cost link to jobs.


So you can see that there could be over 10,000 record spread across 8 tables to give them a snap shot of job profitability. And they want is broken out by month too.


Works fine with a live calculation but it is slow and if they want to run it for year it could take 15 minutes or more to add it all up.


So my solution was to add a new table that has a record for each month for each job.

Everything is linked to that table.

Every Night at 1am the system post all the live calculations to a number field.


At first I set it to updated the posted data as each record was created, but that slowed down data entry if two user where adding data to the same job at the same time one of them would be the LOCKED RECORD error.


So I have solved the problem but curious how other would handle this situation.  In general the live verse posted data is my second biggest hurtle with FM programming.  #1 will alway be not be able to index a calculation that reference other tables.  But I will see that for another discussion.