Log in to create and rate content, and to follow, bookmark, and share content with other members. Not a member? Join Now!

AnsweredAssumed Answered

Date Relationships using <= or >= operators

Question asked by kdcattran on Feb 26, 2018
Latest reply on Feb 26, 2018 by erolst

Like • Show 0 Likes0
Comment • 11

Good Morning,

I am using two date field from my Table Main to attempt to product results that are using the subject line operators with no luck. Here is what I have configured.

As you can see, the relationship from the Date field that is entered in as a Key for each record is linked to the Last 30 Days field which is also a Date field. The Last 30 Days is a calculated date field and here is how I have that configured.

I've researched the FMP online manual and know that the relationship is not finding any records and therefore not producing the results that I am looking for.

I have tried everything that I can possible think of to resolve this but have reached an end of ideas. Obviously I am doing something incorrect. Can anybody shed some guidance on what it is?

Thank you for your time and assistance.

Kevin

No one else has this question

Outcomes

11 Replies

bigtom Feb 26, 2018 3:37 AM
Mark Correct
Correct Answer
It is not clear which Table you are on and which one is related.

However, the issue seems like it is the sort on the relationship. You can change it in the relationship dialog. If the sort is the wrong order you will get the most recent date vs the furthest one away since they are all in the <= range.
Like • Show 0 Likes0
Actions
keywords Feb 26, 2018 3:50 AM
Mark Correct
Correct Answer
From the look of it you are using a self-join relationship. As it stands, you have two problems that I can see:
1. You have your relationship back to front. The relationship as shown will give you dates that are LESS THAN OR EQUAL TO ( ≤ ) the last 30 days date, whereas what you describe suggests you actually want the opposite, i.e. dates ON OR AFTER 30 days ago.
2. You can use a global field on the FROM side of a relationship, but the relationship will not work if you try to use a global (or unstored calc) on the TO side. Depending on how you have your layout set up, you may have this wrong. The FROM side is the Table Occurrence the layout is based on, and the TO side is the Table Occurrence in the portal.

You will need to fix both of these issues to get a result.
3 people found this helpful
Like • Show 1 Like1
Actions
bigtom Feb 26, 2018 3:50 AM
Mark Correct
Correct Answer
Your calc might also be best to use Get (CurrentDate) - 30 and not Get (CurrentDate) - Day(30)

Your operator also looks backwards.

Workout::Date >= Main::Last 30 Days

Sort on Workout::Date
1 person found this helpful
Like • Show 0 Likes0
Actions
kdcattran @ bigtom on Feb 26, 2018 5:25 AM
Mark Correct
Correct Answer
Good Morning,

Thank you for your response. I believe that I made all the recommended changes but have some further questions as I still can't get it to product the desired result. Here is what I've configured based on what I understood.

Getting a many-to-many relationship now.

I changed the Last 30 Days to the 30 instead of the Day(30) which does provide the same result. I tried this before and could not get it to produce the result and is the reason I used the Day( ). I also turned off the Global storage on this field but thought it should be Globally stored so that it updates this field in all rows daily. It only updates when adding a new record and did not fill the field that became blank when removing the Global setting. I manually added the date on the empty fields.

Here is the results of the changes. Still not getting what's expected. Sorry to be such a novice.

One final note. I have the Layout setup with the Last 30 Days table and not a portal. I thought that as long as the underlying data is correct that it does not matter. Am I wrong? I'm trying to use the result to produce Charts.

Kevin
Like • Show 0 Likes0
Actions
philmodjunk Feb 26, 2018 5:44 AM
Mark Correct
Correct Answer
Base the layout on Main.
Make the calculation unstored.
Like • Show 0 Likes0
Actions
philmodjunk Feb 26, 2018 5:46 AM
Mark Correct
Correct Answer
Come to think of it, if not using a portal, just perform a Find for the records.
Like • Show 0 Likes0
Actions
bigtom Feb 26, 2018 6:07 AM
Mark Correct
Correct Answer
It is impossible to get a many to many relationship with just 2 tables.

You should be on Main table layout.

How do you know you are not getting the correct result? You are relating to many records but you show a table view that will only display the first record in the relationship.

I also do not see where you added a sort on the relationship. Also not clear what table you are showing us.

You have repeatedly stated you are not getting the result you want, but you have not stated what result you expect to see.

Global storage can work for the 30 day calc but it does need to be unstored.
Like • Show 0 Likes0
Actions
BruceRobertson @ bigtom on Feb 26, 2018 7:00 AM
Mark Correct
Correct Answer
Here is no such thing as an unstored global calc field.
The calc needs to be unstored. Full stop.
1 person found this helpful
Like • Show 0 Likes0
Actions
ucharles @ BruceRobertson on Feb 26, 2018 7:11 AM
Mark Correct
Correct Answer
Amen

Sent from my iPhone
Like • Show 0 Likes0
Actions
erolst Feb 26, 2018 7:15 AM
Mark Correct
Correct Answer
I think you are working under the misapprehension that by creating a relationship between two TOs, a layout based on an involved TO is somehow constrained to automatically and/or only display matching records only. Not so.

You will need to grab matching records programmatically via the relationship, or display them in a portal, or use GTRR (which then works like a predefined search/filter) – or write a little script that performs a Find for records where the date >= Get ( CurrentDate ) - 30.
1 person found this helpful
Like • Show 0 Likes0
Actions
erolst @ BruceRobertson on Feb 26, 2018 7:16 AM
Mark Correct
Correct Answer
BruceRobertson wrote:
no such thing as an unstored global calc field.
But there should be, because these beasts would refresh better than the ones we have now…
Like • Show 0 Likes0
Actions

Date Relationships using <= or >= operators

Outcomes

Related Content

Recommended Content