Validating a summary of document lines before adding
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!
-
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
-
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.
Iniciar sesión para dejar un comentario.
Comentarios
2 comentarios