Hi All,
Please advise how do I write a Query rule to fetch case Id and folder id from database ?
Below is the query rule I've written to fetch the case Ids and folder Ids from database. How do I map this to a process Model?
a!queryEntity_22r2( entity: cons!ABC_CASES, query: a!query( pagingInfo: a!pagingInfo( startIndex: 1, batchSize: -1,) ), selection: a!querySelection( columns: { a!queryColumn(field: "caseId"),a!queryColumn(field: "folderId") } ), logicalExpression: a!queryLogicalExpression( operator: "AND", filters: { a!queryFilter( field: "caseId", operator: "=", value: ri!abc_cases.caseId, applyWhen: not( rule!APN_isBlank(ri!abc_cases.caseId) ) ), ) } ) ))
Discussion posts and replies are publicly visible
In the query rule you should add a filter for doc_sent <> 1. So that the rule returns all the cases whose document is not sent already. Apart from this call the rule in the process model script task and store in the a pv. the pv should be of the same data type as the output returned by the rule.
Thanks for the response Harsha Sharma . And once I call this rule in the process model script task, how do I fetch the folder Id based on one case per run to pass it further to SFTP folder ?
Hi ,
Create a query rule with doc_sent <> 1 and call it in a Script Task to store results in pv!caseList (List of CDT with caseId and folderId). Use a sub-process with MNI to handle one case at a time. Inside the sub-process, extract caseId and folderId, create an SFTP folder named clientname_caseId, fetch documents using folder(pv!folderId, "documentChildren"), and upload them using SFTP Send File. After successful upload, update doc_sent = 1 in the database. If the list is large, avoid hitting MNI limits by using paging or batching.
doc_sent <> 1
pv!caseList
caseId
folderId
clientname_caseId
folder(pv!folderId, "documentChildren")
doc_sent = 1
your output is having folder id and case id both right.You can use index() to extract the folder if and case id.
In your main process model call this rule and get the list of cases. then call a sub process (or start process smart service) using MNI. within this sub process you will be having one case and one folder at a time. within this sub process have the sftp smart service to send document for one case. Like wise depending on the number of cases that many sub process instances will trigger and docs will be transferred.
In above rule you have paging done as -1. See MNI usage considerations to verify if your output can reach the maximum activity instances limit permitted or else adjust the paging using loops in process model to get cases and folders in batches.
Thank you for the response Srinivas M How can I create a custom name for folder that needs to be created in "SFTP Create Folder" Smart Service? Once I have the pv!folderid, do I need to create another script task and concatenate these both clientname_caseId, and save in a different output? Also, how can I pass this folder name to "SFTP Create Folder" smart service as in which configuration can this value be passed? please advise.
Yes, create a Script Task before the SFTP Create Folder node to build pv!sftpFolderName using:"clientname_" & pv!caseIdThen pass pv!sftpFolderName to the remoteDirectoryPath input of the SFTP Create Folder smart service.