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 22679 times

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

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

structural-shop.jpg (79.05 KiB) Viewed 22679 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 22679 times

greater-than-or-equal-01-01-2025.jpg (90.9 KiB) Viewed 22679 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

[rpierce]

Hi Pascal,

Yes, the field is a lookup "Auto Fill", getting data from the parent table. So, in the database the date in the lookup field is an integer.

I don't have any formatting on the field in AppGini. The "Unsigned" checkbox is checked if that makes a difference.

Thank you for looking at this!

Ray

[pbottcher]

Hi Ray,

actually I would think that this is a bug in AppGini.

If you want to try a temporary solution you could to the following:

add to the Findings.php -> _init function in the hooks folder the following:

Code: Select all

		
	function Findings_init(&$options, $memberInfo, &$args) {
		
		$tmpQueryFieldsFilters = array_flip($options->QueryFieldsFilters);
		$tmpQueryFieldsFilters[ucfirst('LOOKUPFILED_AUTOFILL')]='`ORIGINATALTALBLE`.`DATEFIELDREFERENCE`';
		$options->QueryFieldsFilters=array_flip($tmpQueryFieldsFilters);
		// ... any other code.
 

where
- Findings = The table that you show on the images.
- ORIGINATALTALBLE = Table that has the reference to the datefield (should be Audit)
- DATEFIELDREFERENCE = Datefield in the the ORIGINATALTALBLE
- LOOKUPFILED_AUTOFILL = Field that you want to apply the datefilter on in out current table (not the original)

Now you should be able to apply the filter with "01/01/02024" or something like that.

[rpierce]

Thank you as always Pascal. I will give this a try and report back....

[rpierce]

Hi Pascal,

I finally got to try your suggestion. I modified the hooks file but still get all records when I try to filter on the date in the findings table.

I think you're right, it's bug in AppGIni. I've posted about it before but no fix has been announced that I know of.

Ray

[pbottcher]

Hi Ray,

could you post the code in here?

[rpierce]

Here you go Pascal....

Code: Select all

	function findings_init(&$options, $memberInfo, &$args) {
		/* Inserted by Search Page Maker for AppGini on 2022-12-02 03:45:45 */
		/**$options->FilterPage = 'hooks/findings_filter.php';**/
		/* End of Search Page Maker for AppGini code */
		
		
		$tmpQueryFieldsFilters = array_flip($options->QueryFieldsFilters);
		$tmpQueryFieldsFilters[ucfirst('aud_date')]='`audit`.`date`';
		$options->QueryFieldsFilters=array_flip($tmpQueryFieldsFilters);
		// ... any other code.


		return TRUE;
	}

[pbottcher]

Thanks Ray,

let's give it another try.

Please use:

Code: Select all

		$tmpQueryFieldsFilters = array_flip($options->QueryFieldsFilters);
		$tmpQueryFieldsFilters['Audit Date']='`audit1`.`date`';
		$options->QueryFieldsFilters=array_flip($tmpQueryFieldsFilters);

The 'Audit Date' must match your caption for the aud_date field.
The 'audit1' is needed as this is a referenced table that get mapped in the query.

Hope that works.

[rpierce]

Pascal,

Once again, you pulled it off! That works

Thank you so much for being a part of the AppGini Community!!

Ray