Calculation via Relationship Doesn't Work

[apd183]

Hello all,

I've run into a problem and I cannot find what the problem is. I'm not sure whether this falls under 'relationship' or 'define fields', but the problems in this board looked more like mine. Any help you can provide is greatly appreciated...

Scenario: (also see attached picture)

I have a list of schedule blocks for an event in the Schedule_Database table. Each block has a day number, which is what day of the event it occurs on.

When displaying data to users I want to show the actual date, so I have the Convention_Information table that holds this data. The Convention_Information table takes the date of the first day (that is entered manually) and calculates the other dates (each date in it's own field).

I then have a Global table where you enter the current year. This determines which Convention_Information record we look at for the dates.

Schedule_Database is related to Global via a dummy global field with the same data in it. It is currently an = relationship, but i've also tried the x relationship to no avail.

Global is related to Convention_Information via the the year field. This is a manually entered text field.

The to display the correct date, I have a Date field in Schedule_Database that is a calculation:

Case(

Day_Nbr = ""; "ERROR";

Day_Nbr = 0; Convention_Information::Wednesday_Date;

Day_Nbr = 1; Convention_Information::Thursday_Date;

Day_Nbr = 2; Convention_Information::Friday_Date;

Day_Nbr = 3; Convention_Information::Saturday_Date;

Day_Nbr = 4; Convention_Information::Sunday_Date;

"NO MATCH")

The Problem:

This calculated Date field will not display any data most of the time. When you first open the file and look at records from Schedule_Database, the Date field is blank. I have, on occasion, been able to get the date field to show, but the method of doing so is not reproducable: I can do the same exact thing many more times and it won't work. Once the dates show up, they stay as long as you have the file open.

Any idea on what is keeping this calculation from working all the time?

schedule_db_diagram.bmp

[Ugo DI LUCA]

Hi,

This won't directly adress your problem that seems more a refreshing issue than a calculation issue. But why not determining the day name according to the real date ?

Otherwise, try a script Refresh [Flush Cache] to see if the calc evolves.

[apd183]

Hello Ugo,

In reality, the date fields could be called Day1_Date, Day2_Date, etc. In this case the event will always and forever run from Wednesday to Sunday, so that's why I named the fields as such.

The reason I'm trying to set this up like this is to keep the maintenance of the file from year to year easy. If I put the actual date in the Schedule_Database, I would have to change it and every calculation/script that uses it - not an easy task in this file. This way I only need the actual date for display purposes.

The refresh script step (flush or no flush) does not appear to be solving the problem.

Thanks for your reply!

[apd183]

Ok, I've got it to work with a few changes...

After further testing I realized the relationship between Schedule_Database and Globals was working correctly, but going from Globals to Convention_Information wasn't working. Looking at the Globals table, the Year field was a global text field, relating to a regular text field in the Convention_Information table. Changing the Year field from a global to a regular text field makes the relationship work every single time. At this time, this is an acceptable solution.

This brings up a new question: is this normal behavior? Should a relationship between a global and regular field work? I know relationships based on two globals work (as that is how the Schedule_Database>Globals relationship is set up), so why shouldn't this work? I will need to do more testing, but this appears to be a bug, no?

[comment]

I know relationships based on two globals work

No, not really. For a relationship to work properly, the match field on the child side must be indexed.

[Himitsu]

My problem is kind of similar to this one. I have a LineItem table that stores the dates that a class is closed. The relationship is between Class:ClassID = LI:ClassID. The date field is just a plain date field. On the class table, I have one field that is a calculation that produces a date like, date01:"weekStartDate + 1", date02:"weekStartDate + 2", ect.. then one calc. field date01UI that states:

If (

class:date01 = classLI:date; "Closed";

""

)

The result will only show the first created date field calc. Why is that? What am I missing or not understanding in the calc or relationship?

[apd183]

No, not really. For a relationship to work properly, the match field on the child side must be indexed.

Then how is my Schedule_Databse>Globals relationship working? Both match fields are globals.

For the Globals>Convention Information relationship, the parent match field was a global and the child match field was an indexed text field. Should this have been working?

[apd183]

My problem is kind of similar to this one. I have a LineItem table that stores the dates that a class is closed. The relationship is between Class:ClassID = LI:ClassID. The date field is just a plain date field. On the class table, I have one field that is a calculation that produces a date like, date01:"weekStartDate + 1", date02:"weekStartDate + 2", ect.. then one calc. field date01UI that states:

If (

class:date01 = classLI:date; "Closed";

""

)

The result will only show the first created date field calc. Why is that? What am I missing or not understanding in the calc or relationship?

Do you mean that only 'date01' will be calculated and 'date02', 'date01UI', etc are not?

Some things to check:

- Make sure the ID field in both tables are the same type (number, text, etc).

- You may have just shortened it because of the forum, but table::field references have two colons: 'class::date01'

[comment]

Then how is my Schedule_Databse>Globa ls relationship working? Both match fields are globals.

It depends on how you define "working". Normally, you would want a relationship to pick only certain records from the other table - for example, only records in a matching year. You cannot pick specific records by a global field, because a global field holds the same value for ALL records.

[apd183]

Ah, got it... there's nothing in FileMaker keeping it from working, but it's not the normal type of relationship one thinks of. In this case I want all the children to be related to the parent Globals table, so a relationship with globals 'works'.

[comment]

If you want all the child records to be related, use the x relational operator. Using a global may look like it's working, but it's not documented. It could break in the next bug fix.