Contains tips for configurators working with Aware IM
#46285 by PointsWell
Mon Feb 19, 2018 2:15 am
Thanks to Bruce et al's posts on EXEC_SP

I wasn't smart enough to fully understand how EXEC_SP worked initially so I had to go on my own personal development journey.

I have a number of BO in my model:
Contact
Contact_Bank
Contact_Bank_Statement
Finance_Cashbook

A Contact may have one or more Contact_Bank (accounts) and a Contact_Bank may have one or more Contact_Bank_Statement. A Contact_Bank_Statement has one or more statement lines which appear as records in the Finance_Cashbook. And then there is some linkage to reference data.

The (abridged) ERD looks something like this (this is obviously not an ERD but the image from the query builder I used):
Screen Shot 2018-02-14 at 13.02.59.png
Abridged ERD
Screen Shot 2018-02-14 at 13.02.59.png (23.62 KiB) Viewed 3805 times


To calculate the current and historic balance requires a SUM across each and every transaction and that to be updated every time that a new Finance_Cashbook line is entered. Too expensive and complex to do in AIM, but not that expensive as a native SQL query.

It looks something like this in a stored procedure (the stored proc is not optimal in its method of calculating the running balance but it is fit for purpose for figuring out the tricky bits).
Code: Select allSET @runtot:=0;
SELECT  `MonthTot`.`Acc` AS `Account`,
        `MonthTot`.`Curr` AS `Curr`,
        `MonthTot`.`TXNYEAR` AS `Year`,
        `MonthTot`.`TXNMTH` AS `Month`,
         CONCAT(`MonthTot`.`TXNYEAR`, '/', `MonthTot`.`TXNMTH`) AS `Period`,
        `MonthTot`.`DEBITS` AS `Money Out`,
        `MonthTot`.`CREDITS` AS `Money In`, 
         @runtot:[email protected]+(`MonthTot`.`CREDITS`-`MonthTot`.`DEBITS`) AS `Balance`

FROM
   (SELECT
        `CONTACT_BANK`.`AccountNickname` AS `Acc`,
        `REF_GENERAL_CURRENCY`.`ISO3` AS `Curr`,
         YEAR(`FCB`.`DateTransaction`) AS `TXNYEAR`,
         MONTH(`FCB`.`DateTransaction`) AS `TXNMTH`,
         SUM(`FCB`.`AmtDebit`)  AS `DEBITS`,
         SUM(`FCB`.`AmtCredit`) AS `CREDITS`
    FROM  `REF_GENERAL_CURRENCY`
INNER JOIN `CONTACT_BANK`  ON `REF_GENERAL_CURRENCY`.`ID` = `CONTACT_BANK`.`ps_Currency_RID`
INNER JOIN `CONTACT_BANK_STATEMENT`  ON `CONTACT_BANK`.`ID` = `CONTACT_BANK_STATEMENT`.`ob_Bank_RID`
INNER JOIN `FINANCE_CASHBOOK` `FCB`  ON `FCB`.`ob_Statement_RID` = `CONTACT_BANK_STATEMENT`.`ID`
WHERE `CONTACT_BANK`.`ID`=22712
    GROUP  BY `TXNYEAR` ASC, `TXNMTH` ASC, `ACC`, `CURR`
    ) AS `MonthTot`;


And this then generates this output:
Code: Select allAccount,        Curr,  Year,   Month,  Period,  Money Out, Money In,  Balance
"EURO Account",  EUR,  2018,       1,  2018/1,         20,      500,      480
"EURO Account",  EUR,  2018,       3,  2018/3,        120,      200,      560
"EURO Account",  EUR,  2018,       5,  2018/5          50,        0,      510
"EURO Account",  EUR,  2018,       6,  2018/6,          0,      100,      610
"EURO Account",  EUR,  2018,       7,  2018/7,          0,      400,     1010
"EURO Account",  EUR,  2018,       8,  2018/8,        250,        0,      760


My misunderstanding was the approach to get this into AIM, as I didn't realise that AIM would capture that output exactly and then just needs somewhere to be put.

I created an object (which I named the same as the SP in the database):
Screen Shot 2018-02-19 at 13.05.38.png
AIM BO
Screen Shot 2018-02-19 at 13.05.38.png (28.76 KiB) Viewed 3805 times


In order to get AIM to play nicely with the returned output the SP needs to be modified slightly to add in the Happy AIM columns: BASVERSION, BASTIMESTAMP and ID:

Code: Select allSET @runtot:=0;
SET @ID:=0;
SELECT 1 AS `BASVERSION`,
        CURRENT_TIMESTAMP AS `BASTIMESTAMP`,
        @ID:[email protected]+1 AS `ID`,
        `MonthTot`.`Acc` AS `AccountNick`,
        `MonthTot`.`Curr` AS `CurrISO`,
        `MonthTot`.`TXNYEAR` AS `Year`,
        `MonthTot`.`TXNMTH` AS `Month`,
        CONCAT(`MonthTot`.`TXNYEAR`, '/', `MonthTot`.`TXNMTH`) AS `Period`,
        `MonthTot`.`DEBITS`*-1 AS `MoneyOut`,
        `MonthTot`.`CREDITS` AS `MoneyIn`, 
        @runtot:[email protected]+(`MonthTot`.`CREDITS`-`MonthTot`.`DEBITS`) AS `Balance`


I added BASVERSION as a Constant, BASTIMESTAMP as CURRENT_TIMESTAMP and ID as an incremental counter. I also multiplied MoneyOut by -1 to make it look nice in a graph.

Last step was to set up the query in AIM:
Code: Select allEXEC_SP 'CB_GetTxnBalByMth' WITH '@AccountID'=LoggedInRegularUser.VPL_FinBankAcc.ID RETURN CB_GetTxnBalByMth


I then created a graph to show the Money In, Money Out and Running Balance by month.

Screen Shot 2018-02-19 at 13.11.46.png
Graph Output
Screen Shot 2018-02-19 at 13.11.46.png (34.48 KiB) Viewed 3805 times


I wanted to put all the various contributions, suggestions and experience posts by everyone else explaining all the moving parts into one worked example and hopefully save anyone coming behind me some time.

My reading list:
Bruce's Post on his journey through Stored Procedures https://www.awareim.com/forum/viewtopic ... 40&p=43493

UnionSystems work around for converting arrays into strings for passing to input parameters in stored Procedures (and a link to Bruce's video on SP use, which I've only just seen) https://www.awareim.com/forum/viewtopic ... 22&p=43414

Capturing the BAS fields https://www.awareim.com/forum/viewtopic ... 63&p=43078

Some Speed questions re FIND v SP https://www.awareim.com/forum/viewtopic ... 69&p=39290
Last edited by PointsWell on Mon Feb 19, 2018 11:36 pm, edited 4 times in total.
#46287 by eagles9999
Mon Feb 19, 2018 3:49 am
Nice post Sean.

Glad you solved it.

Who is online

Users browsing this forum: No registered users and 2 guests