I'm using the exportDataStoreEntityToExcel sail function to pull data from a single table into excel...
The data I'm pulling has a location identifier for each row. Is it at all possible to write the data into excel so that data for each location is on a different sheet ?table has loc1 field2 field3 field4loc1 field2 field3 field4loc2 field2 field3 field4loc3 field2 field3 field4loc2 field2 field3 field4I'd like to get the spreadsheet to have
loc1 field2 field3 field4loc1 field2 field3 field4
tabs :Loc1 Loc2 Loc3
Discussion posts and replies are publicly visible
Likely you will want to loop over a!exportDataStoreEntityToExcel() in this scenario - first querying for the list of unique locations, {"loc1","loc2","loc3"}, then potentially utilizing a!foreach() on those locations to call the export function, with fv!item (each location) in the filters parameter, and fv!index as the sheetNumber. Note I haven't implemented this logic with the export function, but I have done this with the Smart Service in a process model which loops utilizing the same documentToUpdate to write to different sheets. Just some food for thought..
that what I thought you'd say :( if we follow that pattern, doesn't it mean that we will be pinging the db for export for each location -- so 30 locations + 1 ping to get the locations listed out.
Yea, unfortunately I can't think of any other way to achieve this currently other than 31 pings to the DB. I'm hoping we see the deprecated Export CDT to Excel functionality come back in the base product, which would be a big help for this and similar situations. Keep us posted on your progress..
You might want/need to check out the Export CDT to Excel smart service in the Excel Tools plug-in. It's officially "deprecated" now but after testing I've found it still works perfectly well in the latest plugin version / latest Appian version. And since they've never bothered to provide a remotely sufficient replacement, I consider this "fair game".
Can't say I disagree with that, I had to use this component recently to solve an issue with all other Export to Excel mechanisms. Don't tell my team! ;)