Jump to content

Using Less Than and Greater Than in Relationship


This topic is 2500 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Table A:
StartingNumber 
EndingNumber 
Active

Table B:
NumberOne: Calc=1
CalcNumber
Count

I created a relationship TableA::Active to TableB:NumberOne, but I am struggling to finish.
The goal is to get a Count of all Active people matching the CalcNumber - So each record where the CalcNumber >= StartingNumber and CalcNumber <= EndingNumber.  The screenshot show the issue592502c583edc_ScreenShot2017-05-23at8_36_27PM.png.bb98e09a2fc1b4109254b8463ad83af2.png592502c5247a9_ScreenShot2017-05-23at8_35_49PM.png.834a943a0b90089a3d971245d289ddbb.png

Link to comment
Share on other sites

Hi there

You are pretty close in what you describe already, you just need to setup the relationship exactly as you describe, then create a calc field in table A to summarise the result.

Since the advent of ExecuteSQL most expert developers would use an SQL statement to get the result you need without using a relationship on the graph. Often we try to reserve relationships on the graph for the data connections and to show portals, rather than to perform summary's in this way, but for smaller and simpler solutions, it is perfectly fine.

I've enclosed an example file if that helps and a screenshot of the results. I just created two FileMaker windows to show both tables side by side.

 

Example.fmp12

example.png

Link to comment
Share on other sites

5 hours ago, crazybake said:

The goal is to get a Count of all Active people matching the CalcNumber

I find this very confusing. Which one of your tables is the table of People? The Active field is in Table A, but the CalcNumber field is in Table B - so what exactly does  "matching the CalcNumber" mean?

Link to comment
Share on other sites

To make the request simple, I did not include all fields.

Table A is my Contacts Table that has all of the personal information, including the Active field, Starting and Ending Numbers.

What I want to do is Count the records where the Active field is checked, but only when CalcNumber >= StartingNumber and CalcNumber <= EndingNumber for each record. The issue in my screen caps above is that for the first record, I want it to stop counting after it reaches the EndingNumber

Link to comment
Share on other sites

This topic is 2500 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.