multi-valued relationship

[merkaba22]

table1:

name

calc_sum(table2::number)

date

weekoftheyear(date) -- text field, calculated result, always evaluate (for 10/2/06 = 40)

calc_dayoftheweek ("2")

table2:

name

number

date

weekoftheyear(date) -- text field, calculated result, always evaluate (for 10/2/06 = 40)

dayoftheweek -- text field, calculated result, always evaluate (for Monday = 2)

table1 related to table2 by name and dayoftheweek on each side of the relationship:

table1::name, (Jim, for example) = table2::name (Jim)

AND

Table1::calc_dayoftheweek ("2") = table2::dayoftheweek ("2"),

calc_sum(number) works great.

However if I add in a further limitation to the relationship:

AND

table1::weekoftheyear("40") = table2::weekoftheyear("40"),

sum(number) no longer works.

Only clue I have is if I change the addition of the third specification to:

AND

table1::weekoftheyear("40") ≠ table2::weekoftheyear("40"),

it works.

Soooo ... obviously the third spec doesn't work, I just can't for the life of me figure out why.

Any glaring things I am overlooking?

Thanks

[Søren Dyhr]

Why do you use quotes in the arguments in the functions used for each of the comparisons??

The returned format from:

http://www.filemaker.com/help/Functions%20Ref22.html

...integer!!

--sd

Edited October 12, 2006 by Guest
misleading typo

[merkaba22]

I am only using quotes for a point of clarification in the question I am asking -- quotes do not appear otherwise.

Sorry for the confusion.

[Søren Dyhr]

But you are using a relation for each week, this is ofcourse posible, but a pretty common misconception (i think we all have suffered from!) of the relational environments utilization. Take a close look at this thread:

http://www.fmforums.com/forum/showtopic.php?tid/176396/post/204083/hl//

You need an extra layer of records a new table of records in between, utilizing the tunneling features provided from fm7+

--sd

[merkaba22]

I am not sure if the example addresses my question -- but thanks.

To put this another way: if I am using a relationship by day of the week that works, why not week of the year?

I can return values by name AND dayoftheweek, I want to return values by name AND dayoftheweek AND weekoftheyear.

I am sure there's lots I don't get but this seems like a no brainer and I am not sure why its not -- accordingly, it hard to find the answer.

Thanks again.

[Søren Dyhr]

By the look of it does it seem like a typo or a key-field type mismatch somewhere along the line. There is nothing in the multi criteria that points otherwise.

But you do still have to explain me why Jim have his own relations, as well as week 40 has it's own relation??

--sd

[merkaba22]

Thanks ...

I set up a relationship between table1::weekoftheyear("40 ") = table2::weekoftheyear("40 ") alone and created a value list which properly returned values.

Still don't see why I can't put this third aspect into the relationship definition since all parts work.....

[Søren Dyhr]

Still don't see why I can't put this third aspect into the relationship definition since all parts work.....

Flatfile databases works too, given the right environment and premises, but building the relational graph so it would look good on the inevitable black t-shirts found in rock'n'roll, isn't conveying an easily approach when comming back 6 month later for maintenance.

Simpler less repetitive graphs are easier digested, and are more self-explanatory so you perhaps could get away with more concise documentation.

I can't see why you need to have 52 relations to make appropriate filtering for a popups - I know that a button on top of a tab is overruled by the tab functionality, if you not are aware of the tricks to reverse the order.

Or you can have the field requirering the popup be placed in an indiviual record, in a cutup portal, or just comming from a realtion away in the file having 52 foreign keyfields, and one datafield having it's dynamical valuelist attached based on the foreignkey value.

--sd

[merkaba22]

A global solved the riddle --

I never had and do not know how you could think that I "have 52 relations to make appropriate filtering for popups"

You have never seen this file.

[Søren Dyhr]

You have never seen this file.

No it haven't! But I have seen your naming conventions and the need to number fields point's in a weird direction. Becasue if a field have a similar meaning as others next to it .... shouldn't they then be broken out in a separate table??

Did you by the way trace down the typo or type mismatch??

--sd

[merkaba22]

No typo or mismatch -- just needed a global for the date field for all records in table1 and over looked that until after a much needed rest.

All is well -- thanks:)