I am exporting data from DB and storing into excel , in Export DSE to Excel smart service i need to add filters which will only export data for last month . How can i add this filtter to it .
One Stored Procedure need to create which will delete the dats for last month after fetching from DB . How to create this for 2 different entities which will delete data for last month.
Discussion posts and replies are publicly visible
You can use the 'filters' input in the configurations to configure it. Here is the documentation about the same - docs.appian.com/.../Export_To_Excel_Smart_Service.html
so , will it be like a!queryFilter(now()-1) for getting last months data .If the process runs once on start of each month
a!localVariables( local!endOfMonth: eomonth(now(), 0), local!splittedDate: split(local!endOfMonth, "/"), local!endDateWithTime: datetime( local!splittedDate[3], local!splittedDate[1], local!splittedDate[2], "23", "59", "59", "59" ), a!queryLogicalExpression( operator: "AND", filters: { a!queryFilter( field: "createdOn_dtm", operator: "<", value: local!endDateWithTime ), a!queryFilter( field: "createdOn_dtm", operator: ">", value: datetime(year(now()), month(now()), 1, 00, 00, 00, 00) ) } ) )
Thanks ,but i could not understand properly .
As the documentation suggests, I have added a logical expression here which allows assigning multiple filters at once. So I have added 2 filters. One for checking that the date in the DB should be greater than the first date of this month and also less than the last date of the same month.
How can i create a stored procedure to delete data from DB for last month for 2 tables.
You have to create the stored procedure into DB and use that stored procedure in Appian by using Execute stored procedure function/Smart Service. Alternate Way is you can use delete from data store entity to delete the data from DB. for this initially, you can fetch the primary keys of the particular entity by using the filter and queryEntity and use those primary key variable in identifier config of the same Smart service or function(delete from datastore entity). Other than that, you can use query database smartservice as well to delete the data from DB based on conditions.