Show one instance of related record with date range

[madman411]

Hi all. I believe this has a simple solution, but I've been unsuccessful.

I have a table of records (table "b" - "Entries") shown in a portal on table "a" ("Tester"). Table "C" shows the occurrences without duplicates. These records have two values that need to be "summarized" within the portal. Furthermore, in table "a" I have two global fields, "start date" and "end date."

To further breakdown the related records I have a relationship filter: "start date <= check date" and "end date >= check date". I am able to get the portal to show each instance of the related record within the date range, however I want one instance of the related record to show with a summed total from within the date range.

In the attached file, portal 2 should show all records that fall within the date range. This is accurate.

In the attached file, portal 3 should show all records that fall within the date range, without duplicates, however, while the occurrence names are correct, the sum doesn't account for the date range. Instead it is summing all records regardless of the specified date range. EDD should be 150. CAPS should be 25.

Can someone tell me where I'm going wrong?

tester.fmp12

[comment]

This is difficult to understand because of the abstract table names. What does the table Tester represent in real life? Is it a real parent table, or just a viewer?

[madman411]

11 hours ago, comment said:

This is difficult to understand because of the abstract table names. What does the table Tester represent in real life? Is it a real parent table, or just a viewer?

Hi comment. Sorry about that. I've tried to strip down and simplify an existing database that contains personal information. I've uploaded a new version of the file with more accurate naming. In the original file, which is aimed at independent contractors, the Tester table is a "tax year" table. "Payment entries" tracks payments made by payroll companies which include a check date. There are multiple payroll companies and some repeat. So if 2022 is the record in the "tax year" table, all payment entries for the 2022 calendar year appear in portal "1".

The purpose of portal "3" is to assist in unemployment applications. The unemployment agency requires some specified date ranges where the amount made by each payroll company needs to be entered. In this example, EDD and CAPS may have paid multiple times over the course of 12 months, however the user might be asked to provide the total payments received within a specified date range, whether it's 2, 3 or 6 months. Instead of having a portal filled with EDD and CAPS payments, my goal is to have one EDD record show and one CAPS record show with a sum of the total payments within the range.

I thought summing via the payroll company table behind the relationship would achieve this, and it does in the sense that it shows one instance of the payroll company that falls within that date range, but the sum calculation field doesn't seem to consider the date filter.

testerV2.fmp12

[comment]

If you're looking for a simple solution, then consider putting portals aside and produce a summary report from the payment entries table instead.

If you do want a portal for this, then you have 2 options:

1. Move the global date fields to the payroll companies table and use them to define a relationship to another occurrence of payment entries table. Sum the related amounts and show this in the #3 portal. If there can be payments that fall inside the date range but in another tax year, then you'll need to also add the tax year to the match fields;
2. See the Portal_Unique_11 file posted here: https://fmforums.com/topic/71906-getting-more-out-of-filtered-portals-3unique-values/?do=findComment&comment=340208&_rid=72594

 

[madman411]

3 hours ago, comment said:

If you're looking for a simple solution, then consider putting portals aside and produce a summary report from the payment entries table instead.

If you do want a portal for this, then you have 2 options:

1. Move the global date fields to the payroll companies table and use them to define a relationship to another occurrence of payment entries table. Sum the related amounts and show this in the #3 portal. If there can be payments that fall inside the date range but in another tax year, then you'll need to also add the tax year to the match fields;
2. See the Portal_Unique_11 file posted here: https://fmforums.com/topic/71906-getting-more-out-of-filtered-portals-3unique-values/?do=findComment&comment=340208&_rid=72594

 

Worked a treat. I had the relationship backwards. Thanks again, comment.