Validating a summary of document lines before adding

Julian Biggin

Hi,

I'm trying to look at validating a summary of a documents lines prior to the document being added.

I have a UDF on the Item Group that sets a ceiling for the total value that can be added without requiring a certain level of authority.

For example, against Item Group A the level is £500 and group B has a level of £750. The user adds five lines, the first two for group A items, each line total is £200 therefore totals to £400 for the group and is under the approval value of £500 but lines 3-5 have a value of £300 each, totalling £900 which exceeds the £750.

If one or more grouped value is greater than the related UDF check amount, I need to stop the document being added.

This is a rough query that gives the results if the document has been added however, I need to stop BEFORE the document is added:

SELECT
    T3.ItmsGrpCod, T3.ItmsGrpNam, T3.U_ApprovalValue,
    SUM(T0.Quantity * T0.Price) as 'Value'
FROM RDR1 T0
    INNER JOIN ORDR T1 ON T1.DocEntry = T0.DocEntry
    INNER JOIN OITM T2 ON T2.ItemCode = T0.ItemCode
    INNER JOIN OITB T3 ON T3.ItmsGrpCod = T2.ItmsGrpCod
WHERE T1.DocNum = 'CurrentOrder'
    AND SUM(T0.Quantity * T0.Price) > T3.U_ApprovalValue
GROUP BY T3.ItmsGrpCod, T3.ItmsGrpNam, T3.U_ApprovalValue

Is this possible with a LineLoop? I can't seem to find a way to achieve this. Any ideas would be appreciated!

Comments

2 comments

Date Votes
  • Comment author
    Geoff Booth

    Just some thoughts:

    • Create a multi-step macro to :
    • Use a while loop (battleshipcobra.com/youtube-download/while_loop_example.txt ) to write the Item Group and Line Value to a temp table
    • Build a query against the temp table such as yours above
    • Launch a UF type MessageBox to block the add if the query returns any values
    0
  • Comment author
    Julian Biggin

    Thanks for the suggestion Geoff!

    In the end I had looked further at the possibility of using a line loop and decided I may as well write this via C# to get the result I needed as this was probably easier to read/adapt in future.

    0

Please sign in to leave a comment.