Date Difference in GI

Do you have a value in the Schema field?  On the Schema Field for that column on your generic inquiry, pick a field that has a numeric type of data (i.e. something with quantity or price should work).  This will tell the system that you want it in that format of data.  It may add some decimal places depending on your settings.  Then the column should pick up the ability to filter by greater/less than.

This is a GI that I have where I have a calculation where that lets me filter by greater than / less than / etc.  Different tables and stuff than yours, but you should get the idea.  Here I’m using a rate field because it’s one of the few numeric fields available with the tables in use. 
 

@meganfriesen37 ,

Thank you for your response.  I did try that with different schemas with a quantity type of output.  But I receive an error when I attempt to sort.

The error is what I receive after I do the sort attempt.

Thank you,

Trisha

@meganfriesen37 ,

Thank you!  Chaning CInt to CDec fixed my issue!

Thank you,
Trisha

@meganfriesen37 - Unfortunately, we’re taking a date field and converting it to a numeric field.  So the schema will be different on the calculated field.  While your solution works because the time spent and the EmployeeRate are numeric in nature, they are different than a date field… 

However, there are a few things to consider:

1. Data Type Consistency: The data type of the Schema Field should match the type of data your formula produces. Since your formula produces an integer (number of days), the Schema Field you choose should also be an integer field.

2. Field Relevance: Ideally, the Schema Field should be related to the data you're working with. While it's not strictly necessary that the Schema Field be contextually related (since you're overriding its value with a formula), it helps maintain clarity.

3. Precision and Scale: If the Schema Field you select is typically used for currency or other precise decimal values, Acumatica might apply formatting that includes decimal places. This isn't usually a problem, but it's something to be aware of.

4. System Behavior: Even with these settings, Acumatica may or may not allow sorting and filtering on a calculated field. It often depends on the underlying system behavior and how the GI is executed and rendered.

It is worth a shot.  However if it doesn’t, read on as it could cause inconsistencies…

------------

@tveld - In Acumatica, when you use a formula to create a new field in a Generic Inquiry (GI), the results of that formula is not stored in the database. They are calculated on-the-fly when the GI is run. This dynamic nature often means that sorting or filtering directly on those calculated fields doesn't work as expected because the database doesn't "know" about these fields; they aren't part of the database schema.

The sorting functionality might not directly support custom fields created using formulas from two separate tables, especially when they involve date calculations or conversions like CInt. However, there are ways you can work around this to provide end-users with the ability to sort or filter based on the date difference.

Here are some steps you might consider to enable sorting or filtering based on the date difference:

1. Create a DAC Extension

If you haven't already, consider creating a DAC (Data Access Class) extension to store the calculated date difference. This way, the date difference becomes a part of your data model and can be treated like any other field in terms of sorting and filtering..

public class AMProdItemExt : PXCacheExtension<AMProdItem>
{
    public abstract class dateDiff : PX.Data.BQL.BqlInt.Field<dateDiff> { }

    [PXInt]
    [PXUIField(DisplayName="Date Difference")]
    public virtual int? DateDiff { get; set; }
    // You might need to implement a field updating event to calculate and set this value
}

2. Calculate and Store the Date Difference

Instead of calculating the date difference in the GI directly, calculate it when the records are inserted/updated. You can do this in a RowInserted/RowUpdated event in a graph extension. This way, the date difference is always up-to-date and stored directly in the database.

protected void AMProdItem_RowUpdated(PXCache cache, PXRowUpdatedEventArgs e)
{
    var row = (AMProdItem)e.Row;
    if (row != null && row.ProdDate != null && row.SOOrder?.RequestDate != null)
    {
        var dateDiff = (row.SOOrder.RequestDate - row.ProdDate).Days;
        var ext = cache.GetExtension<AMProdItemExt>(row);
        ext.DateDiff = dateDiff;
    }
}

3. Modify the GI to Use the New Field

In your GI, instead of calculating the date difference in the formula, directly use the new field you've added (AMProdItemExt.DateDiff). This should inherently support sorting and filtering because it's a standard integer field.

4. Ensure Indexing (if necessary)

For large datasets, you might need to ensure that your new field is indexed properly in the database to avoid performance issues. This is something your database administrator can help with.

5. Test the Sorting and Filtering

After these changes, you should be able to sort and filter the date difference in your GI as you would with any other field. It's always a good idea to test these changes thoroughly to ensure they meet your end-users' needs and that performance is acceptable.

However, if modifying your DAC and database structure is not ideal or feasible, you might consider other approaches:

1. Using a View in the Database: If you have access and permission, you could create a view in your database that calculates this date difference. Then you could include this view in your GI. This might allow sorting and filtering since the calculation is done at the database level.

2. Post-Processing in Excel: If the dataset is not too large and this is a one-off or infrequent need, you might consider exporting the GI results to Excel and doing the sorting/filtering there. Obviously, this is not ideal for large datasets or frequent use.

3. Customization: If you're comfortable with Acumatica's customization tools, you might be able to create a custom action on your GI screen that processes the records after they are retrieved but before they are displayed, ordering them according to your needs.

The approach you choose will depend on your specific needs, the frequency with which you need to perform this sorting/filtering, and the resources you have available for customization or development. If you're not sure, it might be worth discussing with your Acumatica partner or a developer who is familiar with your specific setup.