I’m modifying a dashboard which shows information for the Invoice transactions, if an invoice line has a contract related I want to display the group (FSContDt.ProdCode) based on the Max value for the Ext. Price (this is a calculated field) for all the lines in this case Line 4, the way I want to achieve this is creating an inner subquery to get the Max Ext Price then create a top level query to link the Company, ContractNum and MaxExPrice to the same table (FSContDt) and get the group value, the problem would be if there is more than one record with the same ExpPrice, what would be the best approach in your experience to handle this scenario?
where do you want to do this and when, i.e. BAQ, BPM or Form Customization ? edvView change row, when loading the form, adding, or/and updating records etc ?
so what stopping you from adding the Group field to your SubQuerey and tick it as Group By as well as Company and Contract Name, and use the aggregate function in calculated field to find the maximum value, unfortunately i do not have this module in my environment to test it
Thank you @A.Baeisa and @louis.fequet, yes I’m trying to do this in a BAQ, this a BAQ that our customer created so I’m modifying it to accomplish what they need.
I could get the line from the contract that has the max Ext Price value, what I’m dealing with is to get the partnum related to that max value to link it with the part table and get the product group and the business line (this is a custom field).
If I add the PartNum to the query I get multiple records instead of getting just the one related to the Max value.
With the query I got the Max value I created a subquery to link it again with the FSContDt table (Company, ContractNum and MaxExtPrice) and get the Part but I coudn’t link the table because the ExtPrice is a calculated field so another issue, I read about subquery criteria in the forum but I’m not sure if it can be applied to this case.
SQL’s Row_Number (CalculatedField) is probably your best shot with a where criteria (CalculetedFieldName = 1) on the SubQuery. Then change your relation to Company, ContractNum.
Don’t have access to my stuff, try this in your Test environment (from memory):
select JobNum, AssemblySeq, OprSeq, Row_Number () over(partition by Company, JobNum, assemblySeq order by OprSeq ) as RN
from erp.JobOper
… see what it does. You’ll notice RN will contain the sequence in wich operation appears. Now, if it’s used in a Subquery, you could put a criteria where RN = 1…wich will select the first row of each partition. Because there is an order by clause, one could decide to sort in descending order.
Maybe you could partition by ContractID, order by ExtPrice desc et pick the first row of your partition in your SubQuery. If you decide to do so, use it in a calculated field…obviously.
Good Luck!
EDIT. Something like:
Select ContractID, PartNum, ExtPrice, Row_Number () Over (partition by ContractID order by ExtPrice desc) as RN from YourTable
