Get exceptions/requirements order by the screen displaying.

[tamar]

Hello.
I have to retrieve exceptions and requirements from the database according to the order and hierarchy they are displayed in the screen.
I cannot use Number field because there are items without number.
Any idea how to do this?

Thanks!

[tamar]

Any answer? LMK if the question is not clear...
Thanks!

[BobRichards]

You can get exception/requirement information directly from the SQL tables. Remember to never write to the tables. This will not change the information in the order. Use this expression to experiment with. (Ignore the columns ending in the "@" character.)

This is all the available information. If the item doesn't hae a

Code: Select all

declare @orderNumber varchar(30) = '2023040008'
declare @rootID int = 
	(SELECT [RootID] FROM [pf].[Order] where Number = @orderNumber)

SELECT *
FROM [pfm].[Phrase] p
join [pf].[OrderItemType] oit -- Add Exception/Requirement types
on p.Type# = oit.ID
join [zref].[NumberedListFormat] nlf -- Add Number format
on p.NumberingScheme = nlf.ID
where RootId# = @rootID

If a numbering scheme is not set, you will have to get the ordering information directly from the order object.

[tamar]

This helped me. Thank you so much!