I am trying to do some basic background color changing using the configureCell function of a power table.
The gist of the task is to highlight a value red if it fails to be within a tolerance.
This is easy when the tolerance is a fixed value.
However the issue is that each row can potentially have a different tolerance. And to extract that i have had to use a DB query which is time intensive and lags the client badly.
I don’t really want to fire the configureCell function all the time, rather just once per table initialization. Is this possible?
Somehow loop through all cells and do the processing required just once?
You can store your calculation result in a global (system.util.getGlobals) so you don’t have to do it every time.
You can call a project script from the configureCell function. In that project script, you can have the calculation part of the code outside your function, so it creates variables that are accessible in your function, but not created there.
You can calculate the threshold into invisible columns. This is possible f.e. when everything you need comes from a DB, you can use a query to also include the threshold , hide those columns, but use them for coloring.
You can create your own intermediate dattaset: make a custom property (a dataset) with the origininal values, and decide when you need to update the thresholds. Copy the data from that custom property to the visible dataset when needed, and use hidden columns for coloring.
I personally like bringing it to an external script, as you can put everything time intensive (like a custom import statement, a cachable calculation, …) outside the function body, and it won’t be called for every cell.
It is best to never call a database in foreground scripts (like configureCell). Consider using a query binding to obtain all of the thresholds at once, for all of your rows. This could be a join that includes them in your table data (and then hiding the columns) or a separate query to a custom property on the table. Your configureCell code would then only perform property lookups and dataset lookups. Much, much faster.
Thanks for the responses. I have got it working doing the following:
PropertyChange script on “data” changing. Loop through rows and determine what the thresholds will be for each row. At end of loop, create new column for thresholds. Hide this column. Ensure this script only creates column if not already created.
configureCell script then just looks at the value in the new column for the threshold and returns background color as appropriate. Ensure this script only runs if new column created.
I’m now finding that i can’t keep one of the column widths to a nice width without truncating some data. I have tried to auto resize it, but every time the table data is updated, it creates a new dataset with the new column, and defaults back to a smaller width. Is there a way to force this to a certain column width?
Cheers
If you are going to make a new column, make a custom dataset property for your original binding. Then your propertyChange routine would look at changes to the custom property, and unconditionally write the new dataset to the data property. Avoid overwriting the values supplied by bindings–it’s almost impossible to avoid race conditions.