Excel Export on Updateable Kinetic Dashboard with Search Criteria

Kinetic ERP
,
1

I am re-creating a classic updateable dashboard for Supplier Price List. The primary reason we use this dashboard and not the standard import / export routine is because of subcontract operations.

Previously (Epicor 10.2) this dashboard had a tracker view that allowed the user to select a supplier and effective date and it would return the current price list. The user would then right-click and copy to excel. They would make their price changes in excel, update the effective date and then they would come back to the dashboard with a blank screen (no searching) and paste in the new records. I can get this to work in the Kinetic dashboard from a base update and add new perspective. What I cannot get to work correctly is the export to excel option. If I choose export to excel with the where clause populated, I either get a bad SQL statement or an excel file that simply states the execution time.

If I take the filters off and let the grid show the query contents wide open then the export to excel works correctly. This tells me the issue is in the where clause in the grid options/BAQ provider model. I have tried all sorts of different things to try to get this to work correctly, but I cannot seem to figure out why it will display the correct data in the grid per the filter, but when the export runs it gives a bad SQL statement (the error in the log is unable to convert date to string). I am comparing a field called KeyFields.EffectiveDate (datetime) to the VendPart_EffectiveDate field from the BAQ. they are both date fields albiet one is just plan date and the only options for key fields is datetime. I tried adding a cast to the where clause and that did not work at all. Has anyone run into this where the grid shows the right data per the filter but the export to excel does not work? Interesting enough if you choose the export selected to excel option the highlighted row will export, but that is not what I need. I need the entire result set to export based on the filter, some suppliers could have 3k records or more to export so using the copy all with labels is not ideal as my user would have to scroll forever to load all the records before selecting that option.

See attached for a walk through of the DB and the where clauses I have tried and results for each. What I have found is the only way to get data to actually populate on the export to excel is when there are no filters applied to the grid. If I do that all the results of the BAQ export even though the erp BAQ event is run and shows the filtered records. So that is a long winded way of saying how do I get the export to excel option to work on a panel grid that has a where clause?
ExportSupplierPriceListtoExcel.pdf (1.2 MB)

1 Like
2

Apparently there is a new feature in 2024.1

image
image1428×477 39.8 KB

So:

  • Add Filters
  • Select the rows
  • Overflow “Export with Selected to Excel”
3

I appreciate the feedback but as I noted in the problem, we have some suppliers that will have 3k items on the price list for export. This requires the user to first go to the 3 dot ellipses and change the options to return more than 500 rows for the grid/select, scroll until all 3k records are loaded into the grid and then select all to export selected. The export selected does work (albeit I have to do it twice to actually get all 3k records) in 2024.1. It is not easy for a regular user to do all these manual options and export twice. As you can see in the video, the first export everytime I test it is only 100 records even through you can see there are all 3110 selected in the grid after going to options. Each time I export it the second time the full grid comes out with all 3110 records. It is just frustrating, you would expect the regular Export to Excel to work and if not you would hope we could force the grid to load all the records off the bat everyime. The provider model is set with a page size of 10k, but the grid options that limit to 500 do not seem to be able to be controlled in App studio, and even after you change it manually you still have to scroll to the bottom to load them all in before hitting select all. I hope this gets better in 2024.2.

4

You can turn that off by in a layer so they don’t have to do that. I’d suggest you could have a button to do the select, even the export I am thinking