I'm using dynamic column headings for a series of tables which represent a moving year of data. Each month when new data comes in the last month drops out of the table and a new month is added. The solution works fine to ensure the columns display in the correct order even though it doesn't always take the verbatim column name, changing "%CH1MO" to "Ch 1 mo" for instance, but I guess I can live with that. What I can't abide is not displaying the format correctly.
In particular, the tables often want to format percentage data as Tags. All the data from a query, for the columns in question, comes in formatted as XX%. But often the data qets switched to a Tag format.
I can force each case to String since some “NAs” are mixed in but, when the data is updated each month, I suspect it will revert to Tags for the new months.
When using dynamic column headings, how can I ensure the data will be the correct format each time new data loads. Or, if I fix it once, will it stick with subsequent updates.
I don’t want to have to fix this every month for new data.
I believe you are going to need to parse the item for the dynamic column format and then determine the right type based on what you see.
For example, the percentage columns in your example might be picked up with {{ item.includes(‘%’) ? ‘percent’ : ‘auto’ }} in the Format setup – however this would try to convert a string to a percentage value which might not give you the best results. It also wouldn’t help with values like ‘%CH1MO’… but you could add that into your formatting check discretely {{ item === ‘%CH1MO’ ? ‘string’ : item.includes('%') ? 'percent' : ‘auto’ }}
Thanks! I see where you’re going with that. I’ll give it a try. Using includes(…) should take care of most cases. I can also change the column names in the query to create consistent patterns.
I believe what @pyrrho said is spot on here. Once configured in the dynamic column settings, the format logic will persist across data updates. The dynamic column configuration is part of your table component setup, not tied to specific data instances which makes this easier.
Let us know if that helps fix it. If not, provide a few details on why not and how its still breaking and I can dig into further what else to try.
The dynamic column labels work fine (even though it still changes the text like Feb-25 displays as Feb 25 ) but the table component itself consistently chooses the wrong format for the columns. Data which comes in looking like $4,560 or $12,867 will randomly change to Tag format. Or display in String format but with the comma and dollar sign stripped from the query text. Is there any way to force it not to do this?
Why isn't there just a setting to accept the data as it's formatted from the query including the column headings, case and all, and the order of the fields? Other tools we use don't attempt to change the data, displaying as intended. This behavior is ok with fixed data but won't fly with dynamic data.
One additional thing worth checking is if you previously configured columns manually ( before enabling dynamic column settings ) those saved column configs may still be present and overriding your dynamic format rules.
The item variable in dynamic settings only applies to columns not already covered by a manual column entry. So any column that key matches a previously set column will use that old config ( which may have Tag format ).
To verify, open the Columns section of the Inspector and check whether there are any pre-existing columns listed with Tag format.
If so, either remove those manual entries or update their format directly. For a clean slate, you may want to clear all manual column configs and rely entirely on the dynamic column settings with the item.includes('%') format logic.
Thanks. Good suggestion. I tried that to no avail. I switched the dashboard to Draxlr.com which had the drilldown capability I wanted and didn't mess with the data format. Turned out to be a more elegant solution anyway. Column headings are verbatim from the query.
) but the table component itself consistently chooses the wrong format for the columns. Data which comes in looking like $4,560 or $12,867 will randomly change to Tag format. Or display in String format but with the comma and dollar sign stripped from the query text. Is there any way to force it not to do this?