How I can start/call a process model once when any interface is loading for the first time.
Discussion posts and replies are publicly visible
As Mike notes, there is no way to start a process on form load without user interaction - this would require something such as a button that is displayed in place of the form initially with something like "Load Task", which forces user interaction and calls a process and un-hides the remaining interface. However if this logic is for something such as locking a task in a busy environment, it may not run very cleanly. What is the use case?
Alternatively, I will note that if you can move your updates to a stored procedure, you can call this into a local variable which will fire as soon as the form loads. In this example, as soon as the interface is loaded, dbo.usp_ChrisTestInsert is called and a value is written to the database.
a!localVariables( local!spResult: fn!executestoredprocedure( dataSourceName: "java:comp/env/jdbc/sql_lookup", procedureName: "dbo.usp_ChrisTestInsert", inputs: { {name: "PMID", value: 1}, {name: "TASK", value: 1}, {name: "ASSIGNEES", value: tostring(loggedInUser())} } ), a!formLayout( label: "Test Form", contents: { a!textField( readOnly: true, value: local!spResult.parameters.RESULT ) } ) )
Some posts crossed here, fast moving thread! Just wanted to add that the stored procedure option does require a plugin from the App Market, fn!executestoredprocedure() is not OOTB: Execute Stored Procedure
Good thinking, I'm admittedly not super well versed in SPs so it didn't occur to me that you could call one from the context of an initial variable load, in such a way that it performs updates externally - but it does make sense. I'm curious if you'd be willing to post a simple example SP that would do this (such as just writing a new row to a simple logging table or something), as it might be fun to play around with.
Sure thing! We don't use this type of logic in more than a few areas, but one use case is for our Proxy functions where we actually write to the database at task assignment time - say an assignTask() rule takes in the employee and pm!id, checks the database for any proxy assignments for that PM/user, if so it changes assignment to the proxy and writes the assignment change to an audit table. Handy!
For a basic proof of concept with the example I posted above, table creation:
CREATE TABLE [dbo].[chris_test_usp_insert]( [id] [int] IDENTITY(1,1) NOT NULL, [field1] [varchar](100) NULL, [field2] [varchar](100) NULL ) ON [PRIMARY] GO
Stored procedure:
CREATE PROCEDURE [dbo].[usp_ChrisTestInsert] @PMID int, @TASK varchar(100), @ASSIGNEES varchar(max), @RESULT varchar(20) OUTPUT AS BEGIN SET NOCOUNT ON; INSERT INTO dbo.chris_test_usp_insert (field1,field2) values (@TASK, @ASSIGNEES) SET @RESULT = @ASSIGNEES RETURN END GO
Interface, each load or Test will log a row without interaction
The most generic use case I've seen in the past and would try to handle using this functionality would be like a basic user "last login" marker in the DB, where we can tie it to their DB user id and so forth, without having to do a daily/nightly scrape of the Appian login audit log file, which is clumsy and messy. Thanks for providing the code, I'll try to go through it sometime soon.
My pleasure :). Good use case there for sure with login tracing. One other case we utilize this in is also for task assignment, basically for a "Round Robin" functionality where during task generation/assignment the database is updated for the last assignee (persisting group membership also) and the next one is selected, so multiple tasks can come in and assign correctly prior to the previous one even being viewed.
That SP is a tad more complex..
CREATE PROCEDURE [dbo].[usp_RoundRobinAssign] @PMID int, @TASK varchar(100), @ASSIGNEES varchar(max), @RESULT varchar(20) OUTPUT AS BEGIN SET NOCOUNT ON; DECLARE @WRK_UUID nvarchar(75) = (SELECT CAST(NEWID() AS nvarchar(75))) DECLARE @ASSIGNEES_WRK varchar(max) = @ASSIGNEES -- ADD ASSIGNEES TO TEMP TABLE FOR VERIFICATION WHILE @ASSIGNEES_WRK <> '' BEGIN IF CHARINDEX(',',@ASSIGNEES_WRK) = 0 BEGIN INSERT INTO dbo.tblRoundRobin_IdCheck (assigneeId,UUID) VALUES (CAST(@ASSIGNEES_WRK AS varchar(20)),@WRK_UUID) SET @ASSIGNEES_WRK = '' END ELSE BEGIN INSERT INTO dbo.tblRoundRobin_IdCheck (assigneeId,UUID) VALUES (CAST(SUBSTRING(@ASSIGNEES_WRK,1,CHARINDEX(',',@ASSIGNEES_WRK)-1) as varchar(20)),@WRK_UUID) SET @ASSIGNEES_WRK = SUBSTRING( @ASSIGNEES_WRK, CHARINDEX(',',@ASSIGNEES_WRK)+1, LEN(@ASSIGNEES_WRK)-CHARINDEX(',',@ASSIGNEES_WRK) ) END END -- INSERT ANY MISSING IDs INSERT INTO tblRoundRobin (pmId,task,assigneeId) (SELECT @PMID AS pmId,@TASK AS task,t.assigneeId FROM dbo.tblRoundRobin_IdCheck t WHERE UUID = @WRK_UUID AND t.assigneeId NOT IN (SELECT assigneeId FROM tblRoundRobin WHERE pmId = @PMID AND task = @TASK) ) -- REMOVE OLD IDS DELETE FROM tblRoundRobin WHERE assigneeId NOT IN (SELECT assigneeId FROM dbo.tblRoundRobin_IdCheck WHERE UUID = @WRK_UUID) AND pmId = @PMID AND task = @TASK -- DONE WITH TEMP TABLE --DROP TABLE tblRoundRobin_temp DELETE FROM tblRoundRobin_IdCheck WHERE UUID = @WRK_UUID -- SETUP ASSIGNEE VARS DECLARE @NEXTASSIGNED varchar(20) DECLARE @LASTASSIGNED varchar(20) = (SELECT assigneeId FROM tblRoundRobin WHERE lastAssigned = 1 AND pmId = @PMID AND task = @TASK) DECLARE @NOASSIGNMENT bit = (SELECT CASE count(*) WHEN 0 THEN 1 ELSE 0 END AS noAssigned FROM tblroundrobin WHERE lastAssigned = 1 AND pmId = @PMID AND task = @TASK) DECLARE @ENDASSIGNED bit = (SELECT CASE count(assigneeId) WHEN 0 THEN 1 ELSE 0 END AS endAssigned FROM tblRoundRobin WHERE pmId = @PMID AND task = @TASK AND doNotAssign = 0 AND assigneeId > (SELECT assigneeId FROM tblRoundRobin WHERE lastAssigned = 1 AND pmId = @PMID AND task = @TASK)) --SET NEXT ASSIGNED IF @NOASSIGNMENT = 1 OR @ENDASSIGNED = 1 BEGIN SELECT TOP 1 @NEXTASSIGNED = assigneeId FROM tblRoundRobin WHERE pmId = @PMID AND task = @TASK AND doNotAssign = 0 ORDER BY assigneeId ASC END ELSE BEGIN SELECT TOP 1 @NEXTASSIGNED = assigneeId FROM tblRoundRobin WHERE pmId = @PMID AND task = @TASK AND assigneeId > @LASTASSIGNED AND doNotAssign = 0 ORDER BY assigneeId ASC END -- CLEAR CURRENT ASSIGNEMT UPDATE tblRoundRobin SET lastAssigned = 0 WHERE pmId = @PMID AND task = @TASK -- UPDATE DB FOR NEXT ASSIGNEE UPDATE tblRoundRobin SET lastAssigned = 1, countAssigned = (countAssigned+1),lastAssignedOn = getDate() WHERE pmId = @PMID AND task = @TASK AND assigneeId = @NEXTASSIGNED -- RETURN NEXT ASSIGNEE ID SET @RESULT = @NEXTASSIGNED RETURN END GO