Auto-complete a form without user interaction

Comments

8 comments

  • Avatar
    AgilePoint NX Support

    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

    -1
    Comment actions Permalink
  • Avatar
    Loren Bratzler

    Paul,

    Couple of questions:

    1. Why would you use a form at all if there is no user interaction required?

    2. 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!
    1
    Comment actions Permalink
  • Avatar
    Paul Horvath

    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.

    0
    Comment actions Permalink
  • Avatar
    Loren Bratzler

    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

    1
    Comment actions Permalink
  • Avatar
    Paul Horvath

    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.

    0
    Comment actions Permalink
  • Avatar
    Loren Bratzler

    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.

    1
    Comment actions Permalink
  • Avatar
    Paul Horvath

    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

    0
    Comment actions Permalink
  • Avatar
    Loren Bratzler

    Great to hear Paul!  I'm glad this worked for you!!

    0
    Comment actions Permalink

Please sign in to leave a comment.