From my understanding, non-persisted fields are the best way to handle data that needs to be calculated using some business logic. However, since these fields aren’t saved to the database, we can’t use them in generic inquires and reports. If I instead saved this field to the database, I think I would need to make sure that it is recalculated either every time one of the values in the business logic equation changes, or if a user or anything else is interacting with it, including a report/inquiry. I’m not sure of the best way of doing that or if I’m missing something.
Answer
Save field calculated using business logic to database
Best answer by Dmitrii Naumov
Hi
Well, first of all, calculated fields can be used in GIs/Reports. For that you need to implement the calculation on the DAC level, e.g. via PXDBScalar or PXDBCalced attribute.
If the logic is not too complex, it is the recommended way.
If you want to stick to the idea of saving the value to the DB, the recommended way is to add events that trigger the recalculation.
E.g. if the value depends only on the fields of the same record, you can implement FieldUpdated event for each of these fields.
If the value depends on other data records, it is recommended to implement RowInserted, RowUpdated, RowDeleted for each record type it depends on.
The same can be implemented via PXFormula attribute.
+7Hi
Well, first of all, calculated fields can be used in GIs/Reports. For that you need to implement the calculation on the DAC level, e.g. via PXDBScalar or PXDBCalced attribute.
If the logic is not too complex, it is the recommended way.
If you want to stick to the idea of saving the value to the DB, the recommended way is to add events that trigger the recalculation.
E.g. if the value depends only on the fields of the same record, you can implement FieldUpdated event for each of these fields.
If the value depends on other data records, it is recommended to implement RowInserted, RowUpdated, RowDeleted for each record type it depends on.
The same can be implemented via PXFormula attribute.
In this test GI I created, many of the values that should be 0.00 show up as blank.
When I tried to add to the Customers GI, I get even weirder behavior. The YTD Invoice Total shows as blank for all customers. If I filter by customers who have a YTD Invoice Total not equal to 0, it correctly filters the customers, but the column showing the value is still blank for those customers.
Here is the code I have used. Is this calculation too complex and why we are having the issues or do you think there something else going on?
using System;
using PX.Data;
namespace PX.Objects.AR
{
public sealed class CustomerExt : PXCacheExtension<Customer>
{
[PXDecimal]
[PXUIField(DisplayName = "YTD Invoiced Amount")]
[PXDBScalar(typeof(Search4<ARInvoice.goodsTotal, Where<ARInvoice.customerID, Equal<Customer.bAccountID>, And<ARInvoice.docType, Equal<ARDocType.invoice>, And<DatePart<DatePart.year, ARInvoice.docDate>, Equal<DatePart<DatePart.year, Today>>>>>, Aggregate<Sum<ARInvoice.goodsTotal>>>))]
public decimal? UsrYTDInvTotal { get; set; }
[PXDecimal]
[PXUIField(DisplayName = "Total Invoiced Amount")]
[PXDBScalar(typeof(Search4<ARInvoice.goodsTotal, Where<ARInvoice.customerID, Equal<Customer.bAccountID>, And<ARInvoice.docType, Equal<ARDocType.invoice>>>, Aggregate<Sum<ARInvoice.goodsTotal>>>))]
public decimal? UsrInvTotal { get; set; }
public abstract class usrYTDInvTotal : IBqlField, IBqlOperand
{
}
public abstract class usrInvTotal : IBqlField, IBqlOperand
{
}
}
}
+7
This is maybe a newbie question, but why (or how) is the field IsDefault considered calculated? If it only exists in the DAC, how is that data preserved every time the website is restarted (e.g. when customizations are published)?
For reference, I started here:
I am currently working on figuring out the cause of some connector errors, and I am finding that I do not understand how this data is preserved well enough.
Thanks,
Aaron
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.