Filter on Lookup Field

[rpierce]

Hello,

I've posted about this problem in the past and need to revisit the issue. I have an extensive database built with AppGini. I also have parent tables with several children tables. Many of the children tables get the date from the parent table vial a Lookup field using Auto-fill. Whenever a filter is performed on the date field of the child tables the results are incorrect. I get either all records, no records, and other results. I'm currently using AppGini 24.17 revision 1725

In construction we perform regular Safety Audits of the project. I have an Audit table that contains Header information about the audit. There is a "Findings" child table that gets the Audit Date from the parent "Audit" table. Everything works great until I try to filter the date on the Findings Table. I can filter fine on other lookup fields. It's just the Date fields of Child tables giving me a problem with filtering. Filtering by Date on the Parent tables works as it should.

I have tried to filter the Child table date in several formats:
- As saved in the database 2025-01-01 - No results
- As formatted in AppGini 01/01/2025 - All records as result
- With a format not in the database or AppGini 01-01-2025 - All records as a result.

This post might get lengthy due to screen shots I'm including.

In the image below is the Child table "Audit Findings". There is no filter applied. The lookup field from the parent table "Audit" is "Audit Date" Total records is 3090.

audit-findings.jpg (94.17 KiB) Viewed 237 times

Now I filter out to show only Haskell Structural Shop. Total records found is 123

structural-filter.jpg (38.64 KiB) Viewed 237 times

structural-shop.jpg (79.05 KiB) Viewed 237 times

Now I further filter on the "Audit Date" Field for records Greater than or equal to 01/01/2025. Results, all records

further-filter.jpg (70.55 KiB) Viewed 237 times

greater-than-or-equal-01-01-2025.jpg (90.9 KiB) Viewed 237 times

Experimentally, I replace the forward slash with a dash and still, all records result. I have also tried using the date format as stored in the database - 2025-01-01. This yields no results. "No matches found" message.

I managed to get the date into the "Audit Findings" date field using a calculated field and am able to sort on the date. However, when the "Save as copy" function is used, the date does not update unless each record is opened and saved again. That is very problematic. I'm studying a work around by Jan and AppGini Helper. Maybe that will be the fix for me??

The system won't let me upload anymore images, but I think you get the picture. This is an old problem that keeps resurfacing for me. I hope Ahmed or somebody can help me correct it.

Thank you!
Ray

[rpierce]

As a side note, I just upgraded AppGini to version 25.10 revision 1935 and the problem persists.

[pbottcher]

Hi Ray,

just for clarification.
- the field is a lookup from the parent via auto-fill
- do you have any formatting of field set in appgini?

best