Large Calculated Summary Field Doesn't Load Correctly Immediately and Shows 0 Temporarily...Sometimes
Hi,
So I'm running into an interesting situation where I have a particular calculated/summarized value is summed up across enough data, that if it's been a while since I (and probably others) have been in the system, and I log in and immediately go to the summary report in question, that particular summary value shows as 0. But if I then wait some seconds and reload the page, it shows the correct value.
This seems to me like Quickbase only caches a calculated/summarized result for so long and then at some point resets results to 0 and reruns all the summaries? And then since this particular value is a summary of a lot of data it takes the system some handful of seconds to get the result? That's a bit of a guess based on what I'm seeing at least.
So to tell you precisely the setup of data and what I'm seeing:
I have a list of Project Costs. I then have a table called Project Cost Calcs with one record that is related to every row of the Project Costs table (Project costs table just has a formula field with the value 1 and I set up the relationship for that to be the related field with the one entry in the calcs table). This allows me to do various summaries across the whole table. For example, "Total Project Cost, excluding any contingency entries".
Back in the Project Costs table, I then lookup that summary of total project cost excluding contingency. And then I have a contingency record in the project costs table that has a special field allowing the cost for that record to be some multiple (percentage) of that total project cost, excluding that row (and any other rows marked as being a special contingency record).
I then also have a separate table of project budget codes, and every project cost is associated with one of those codes. In the Budget Code table I then have a summary report that pulls together all the project costs, by budget code.
It's the calculated contingency value that shows up as 0, either in the project costs table or the budget code summary table, sometimes, if I navigate to those locations immediately after logging in. And as I said the database always seems to catch up and if I reload a few seconds later the contingency cost shows up correctly. For reference, the project costs table currently has 1769 records in this particular project app.
Has anyone seen this before? Is there anything I or intuit can do to help this value calculate more quickly? If I think of excel formulas, in general they recalculate every time a value that affects them changes, and then that result stays cached....or at least I've never temporarily seen a 0 in excel. Can Quickbase not do the same? (I recognize Quickbase is vastly different to excel, that's just a go-to comparison)
Ultimately since we're now aware of it it's a relatively low consequence issue, but our client's project manager for this large project generally does precisely that....logs in and goes directly to the project cost summary to see where the total is ending up in this planning phase. So he was the one who brought it to my attention, and responding with, "oh yeah you'll just have to reload after a few seconds whenever it loads wrong" doesn't feel like a great response. But that's all I have for him right now.
I look forward to any help anyone can offer.
EDIT: I should point out as well that the total cost field for each record of the Project Cost table is a calculation in itself of QTY*(Unit material cost+Unit Labor Hours*Labor Rate) + Freight. So that's a whole other level of calculations that need to take place if there is some point in time that calculation/summary cache is cleared and that total project cost summary needs to be re-derived from raw data. I would have thought though that if certain calculations need to be done to present a particular report correctly, then that report wouldn't load until the required calculations had been done?
Thanks,
Leith
------------------------------
Leith Simpson
------------------------------
So I'm running into an interesting situation where I have a particular calculated/summarized value is summed up across enough data, that if it's been a while since I (and probably others) have been in the system, and I log in and immediately go to the summary report in question, that particular summary value shows as 0. But if I then wait some seconds and reload the page, it shows the correct value.
This seems to me like Quickbase only caches a calculated/summarized result for so long and then at some point resets results to 0 and reruns all the summaries? And then since this particular value is a summary of a lot of data it takes the system some handful of seconds to get the result? That's a bit of a guess based on what I'm seeing at least.
So to tell you precisely the setup of data and what I'm seeing:
I have a list of Project Costs. I then have a table called Project Cost Calcs with one record that is related to every row of the Project Costs table (Project costs table just has a formula field with the value 1 and I set up the relationship for that to be the related field with the one entry in the calcs table). This allows me to do various summaries across the whole table. For example, "Total Project Cost, excluding any contingency entries".
Back in the Project Costs table, I then lookup that summary of total project cost excluding contingency. And then I have a contingency record in the project costs table that has a special field allowing the cost for that record to be some multiple (percentage) of that total project cost, excluding that row (and any other rows marked as being a special contingency record).
I then also have a separate table of project budget codes, and every project cost is associated with one of those codes. In the Budget Code table I then have a summary report that pulls together all the project costs, by budget code.
It's the calculated contingency value that shows up as 0, either in the project costs table or the budget code summary table, sometimes, if I navigate to those locations immediately after logging in. And as I said the database always seems to catch up and if I reload a few seconds later the contingency cost shows up correctly. For reference, the project costs table currently has 1769 records in this particular project app.
Has anyone seen this before? Is there anything I or intuit can do to help this value calculate more quickly? If I think of excel formulas, in general they recalculate every time a value that affects them changes, and then that result stays cached....or at least I've never temporarily seen a 0 in excel. Can Quickbase not do the same? (I recognize Quickbase is vastly different to excel, that's just a go-to comparison)
Ultimately since we're now aware of it it's a relatively low consequence issue, but our client's project manager for this large project generally does precisely that....logs in and goes directly to the project cost summary to see where the total is ending up in this planning phase. So he was the one who brought it to my attention, and responding with, "oh yeah you'll just have to reload after a few seconds whenever it loads wrong" doesn't feel like a great response. But that's all I have for him right now.
I look forward to any help anyone can offer.
EDIT: I should point out as well that the total cost field for each record of the Project Cost table is a calculation in itself of QTY*(Unit material cost+Unit Labor Hours*Labor Rate) + Freight. So that's a whole other level of calculations that need to take place if there is some point in time that calculation/summary cache is cleared and that total project cost summary needs to be re-derived from raw data. I would have thought though that if certain calculations need to be done to present a particular report correctly, then that report wouldn't load until the required calculations had been done?
Thanks,
Leith
------------------------------
Leith Simpson
------------------------------
