Query to update per UDT Row

[former_member282786]

Hi

I have created a UDT with the GL Accounts from the COA defined under the Code column and inputted multiple Monthly UDF columns, now these columns that were created as UDF's are called Actual "per month", this Actual UDF needs to pull through all Service AR Invoices Row Totals based on GL Account and Project for the month, so I thought of using the JDT1 Table (as I can do a SUM on Credit Amount and minus this off if there is a Credit in the month by SUM Debit) as this will have both GL Accounts and Project Code but also on the basis a Invoice & Credit note can be taken into consideration.

I am having issues with the query to update the GL Accounts Actual Columns per row in the UDT, I thought of using a Update query in a Stored Procedure called "SBO_SP_TransactionNotification" but this just overwrites the last update with the new one as I have to have this query to update per GL Account Code.

Does anyone know of a query that can help me make this work, especially creating an update query within the Stored Procedure to update the UDT Actual column per row (as this information will be needed for reporting purposes).

Yours Sincerely

Kurt Walters

[Former Member]

You need another where in the end to define which row need to be updated.

[former_member282786]

Hi Gordon

How will the Query look as I have had a look at adding in RowId =1 / RowId ='1' / Row =1 / Row ='1' but all fails, are you able to help on how I need to incorporate the new WHERE clause with Row in the query.

Regards

Kurt Walters

[Former Member]

Try this:

UPDATE dbo.[@UDT]
Set dbo.[@UDT].U_MACTUAL = (SELECT SUM(JDT1.Credit) - SUM(JDT1.Debit)
FROM JDT1
JOIN dbo.[@UDT]
ON JDT1.Account = dbo.[@UDT].Code
WHERE JDT1.Account ='510005' AND JDT1.Project ='103000'
AND JDT1.RefDate >='20100201' AND JDT1.RefDate <='20100228')
WHERE dbo.[@UDT].Code ='510005'

[former_member282786]

Hi Gordon

Thanks very much for your reply, sorry for my late response; I haven't had time to test your query but will update the message when I do.

Thanks once again fo your quick replies.

Regards

Kurt Walters

[Former Member]

Hi Kurt Walters,

You need WHERE clause exactly allocated the right record and SET the right column value.

Thanks,

Gordon

[former_member282786]

Hi Gordon

Can you elaborate more on the WHERE and SET, please see the below query that I am currently using but the 2nd Update always overwrites the 1st Update (as this query will need to be inputted per GL Account needed and at the moment that is 270, so was thinking an INSERT may be better):

UPDATE dbo.[@UDT]

Set dbo.[@UDT].U_MACTUAL = (SELECT SUM(JDT1.Credit) - SUM(JDT1.Debit)

FROM JDT1

JOIN dbo.[@UDT]

ON JDT1.Account = dbo.[@UDT].Code

WHERE JDT1.Account ='510005' AND JDT1.Project ='103000'

AND JDT1.RefDate >='20100201' AND JDT1.RefDate <='20100228')

Regards

Kurt Walters

[Former Member]

If your U_MACTUAL is just one each for each account, you have no way but get the last update only. I am not sure what field/fields in your UDT to reflex GL Account and Project combinations.

[former_member282786]

Hi Gordon

The Project is representing the UDT (so have created a UDT for a Project), so the UDT has the following columns:

• @UDT = is merely an example of the created table name *

The UDT we created is [dbo].[@UDT] with the following fields:

Code = G/L Account from COA

Name = Description from G/L Account

U_MACTUAL = Monthly Actual figure per G/L Account based upon Project Code (these transactions being posted in Service Type AP Invoices)

So I was wanting to get a summed value of all AP Service Type Invoices posted to the relevant GL Account (which is in the UDT) based off selected Project Code; so as an example we create postings on a Service Type AP invoice for the month of March, select account 520005 - Purchases Food Sales > Select Project Code 100000 > then put total, from here we want to retrieve the Actual (U_MACTUAL) for the month of March for this GL Account based off the Project.

The reasoning behind this is that our company has an excel spreadsheet that they had created previously and want to keep this design, so I am wanting to do this via the XL Reporter so we can keep the same format as they have, and if the data is stored in x1 UDT is is easier to get the data from using the "Get Other Data" functionality.

Is it possible to update the UDT per GL Account per month?

Regards

Kurt Walters

[Former Member]

What is your second update? Is it by the same query?

[former_member282786]

Hi Gordon

Yes the same query except for the "JDT1.Account ='510005'" where it will change to the next account code, say '510010' but still based on JDT1.Project ='103000'

Regards

Kurt Walters

[Former Member]

From your query:

JDT1.Account = [dbo\].[@UDT\].[Code\]

Are you using Code in your UDT to hold all of your account codes?

[former_member282786]

Hi Gordon

Correct, the Code field in the UDT represents the GL Account Code of the COA, which is where JDT1 Account = UDT Code, the same applies to the Name field in the UDT it represents the GL Account Code Description but the query I am looking for is based on the Code / Account as this is where I will be able to pick any postings to it (baring in mind the Project that gets posted to this account).

Regards

Kurt Walters