Hi
I have related records :
An item can have multiple prices so there is a 1 to many relationship.
I am wanting to use the custom record field on the Purchase Order record to calculate the cost of the Purchase Order ( Purchase Order. qty * latest pricing for the item)
I have an expression rule that returns the latest price for an item however the custom record fields does not allow us to use expression rules.
Also, since an item can have multiple prices, I am not able to get the latest price from just using the . notation from the related records.
I need to have the cost in a custom record field , as I need to aggregate the cost by supplier.
Please advise on a way to achieve this.
Thanks
Discussion posts and replies are publicly visible
I haven't been able to define a relationship in the way you're describing, but want to share a possible workaround to your scenario. The a!relatedRecordData() could get you what you're looking for.
To get the price associated with the most recent effectiveDate you can use the relatedRecordData parameter in functions a!queryRecordByIdentifier(), a!queryRecordType(), and a!recordData(). Define the relationship to purchaseOrders.items.pricing, limit to 1 related record, and sort by the effectiveDate in descending order.
relatedRecordData: a!relatedRecordData( relationship: purchaseOrders.items.pricing, limit: 1, sort: a!sortInfo(pricing.effectiveDate, false) )
Your calculated cost would then just be purchaseOrder.qty * purchaseOrder.items.pricing.amount.
To display in a gridField you would define the relatedRecordData in the a!relatedData() function and display the value like so...
a!currency(isoCode: "USD", value: fv!row[purchaceOrder.qty] * fv!row[purchaseOrder.items.pricing])
Not exactly what you were asking about, but I hope it helps