Auto-complete a form without user interaction
Hi everyone,
I have created a form that has a collection of subforms. The subform data gets pushed to a SQL DB using the Insert and Update common controls.
When the eForm is set to AutoComplete=False, a user can look at the form and hit submit... the rest of the process executes and the DB is updated.
I would like to have this automated. I though by setting auto-complete to TRUE the form would "render" on the server side and complete everything, however it isn't.
Any suggestions on how to auto-complete a form ?
-
Hi Paul,
When you set AutoComplete to true seems the process moves forward in a fraction of seconds and it's not waiting to get the subform populated with the data to push into the DB, hence DB is not getting updated.
Basically, in your case subform will be empty when AutoComplete is set to True.
Thanks
-
Paul,
Couple of questions:
- Why would you use a form at all if there is no user interaction required?
- What are you referring to when you say "Insert and Update common controls"? Are you talking about form controls? If so, I don't think I have ever seen a form control that allows you to update database tables directly on a form. If there is such a control, I would love to use it!
- Why would you use a form at all if there is no user interaction required?
-
Hi Loren,
I'm new to APNX so I'm not familiar with all of the features. Thank you for your interest in helping me.
The business need is this:
A process is started that allows a person to define a projected savings. The savings are planned out over a 12 month period.
The 12 records of planned monthly savings need to be pushed to a SQL Server table.
Table Columns:
myUID - varchar(50)
mySavingsProject - varchar(50)
MonthOfSaving - date
Saving Amount - money
on the eForm, I use a subform, create 12 rows, and populate the subform properties using javascript. These 12 rows need to be pushed to the database. This all works properly when the user hits SUBMIT.
From what I've read, the OOTB tool is to use the process flow, a loop, and a record insert (or record update). All this works as expected (xref the tutorials).
If I don't use a form (so i can avoid the auto-complete), what would you recommend? I considered using Javascript to push to the DB but from what i've read I would need to use AJAX and I'm not familiar with that tool/code.
-
How is this process initiated? If a user is starting the process via eForm, can you put your Javascript logic to populated the sub-form in the start form itself? Then you should be able to use the database process activities to push that data to the database.
But if the necessary data is not available at Start Form time, could the logic in the Javascript that creates the subform records be pushed down to SQL? Perhaps with the use of a Stored Procedure on the database that you can call from AgilePoint?
Also, regardless of how you eventually get the subform data populated, you might be able to utilize the Batch Insert activity instead of a loop to push the data down to the SQL table:
https://documentation.agilepoint.com/00/appbuilder/cloudenvShapeBatchInsert.html
-
In theory, yes i could move it to a form where the user is selecting the submit. The challenge is that during the main process, these records will be updated various times depending on go/nogo decisions. So I made a subprocess the has the eform/subform table, the loop init, record update, etc. and I am inserting this subprocess throughout the main process.
Ideally, i would like the subprocess to be zero user.
-
It seems like the best fit would be to create a Stored Procedure on your SQL database that you can pass in the relevant data and let it insert/update the records. The Stored Procedure could be called from the subprocess and then you might not need the Subform at all.
You mentioned that you currently have Javascript logic that is populating the Subform. The question would be can you take the logic from the Javascript and put it into the Stored Procedure? If so, then you could pass in the necessary information to the Stored Procedure so that it can determine the values that need to be inserted/updated and do those inserts/updates directly to the tables.
-
My hero! I'd never written a SP and this was challenging for me to do but, as you indicated, it's clean, fast, and elegant.
Thank you very much for the guidance!!
As a side note, if anyone is reading this thread here is my SP... note that if your AP values are NULL/empty then the SP will FAIL when trying to post a number or a date unless you explicitly build code into your SP to detect/handle empty/null inbound values.
ALTER PROCEDURE [dbo].[newVECMonthTable](
@VECID AS VARCHAR(150),
@VECMonth AS DATE,
@VECMonthSaved AS MONEY,
@Phase AS VARCHAR(150),
@Rank AS VARCHAR(150),
@EffPlant AS VARCHAR(150),
@Geo AS VARCHAR(150),
@dbImp AS VARCHAR(150),
@dbTerm AS VARCHAR(150))
AS
/*DECLARE @UID AS VARCHAR(150);*/
DECLARE @FY AS VARCHAR(150);
DECLARE @WorkMonth AS DATE;
DECLARE @MonthCount SMALLINT;
SET @WorkMonth = DATEFROMPARTS(YEAR(@VECMonth), MONTH(@VECMonth), 1);SET @MonthCount = 0;
WHILE @MonthCount < 12
BEGIN
IF (MONTH(@WorkMonth) < 4)
SET @FY = YEAR(@WorkMonth)-1;
ELSE
SET @FY = YEAR(@WorkMonth);
INSERT INTO [dbo].[VEC_Monthly_Report] ([UID],[VECID],[VECMonth],[VECMonthSaved],[Phase],[Rank],[EffectedPlant],[Geo],[FY],[dbImplemented],[dbTerminated])
VALUES (@VECID+Char(65+@MonthCount),@VECID,@WorkMonth,@VECMonthSaved,@Phase,@Rank,@EffPlant,@Geo,@FY,@dbImp,@dbTerm);/* [UID],[VECID],[VECMonth],[VECMonthSaved],[Phase],[Rank],[EffectedPlant],[Geo],[FY],[dbImplemented],[dbTerminated] */
SET @MonthCount = @MonthCount + 1;
SET @WorkMonth = DATEADD(month,1,@WorkMonth);
END
Please sign in to leave a comment.
Comments
8 comments