Relationship not working

[David Holmberg]

I'm working on a relationship between two tables in the same file. One contains records of expenses, the other shows statistics of these expenses.

Some of the fields in the expense file is:

Username - text

Year - number

Month - number

Type - text

Description - text

Cost size - number

Sum cost size - statistic sum of cost size

In the stats table some fields are:

Username - text

Year - number

Month - number

Type1 - text (global)

Type2 - text (global)

Type3 - text (global)

Type4 - text (global)

Type5 - text (global)

In the type field in expenses you write food, petrol, rent etc.

The type fields in the stats table is based on a valuelist from the type field in the expense table.

You choose which expense types you want to get stats of. There are relationships matching user-user, year-year, month-month and type-type1-5.

Here comes the problem: When you have filled the type 1-5 fields and got stats of 5 different expenses I want one field to show how big part the expenses not choosen is.

I created a relationship matching user-user, year-year, month-month and then not matching type with type 1-5 (using the "overwritten" "=". I thought this would make me get the sum of the expenses which not were type 1-5 but no expenses "got through" the relationship.

Why?

[Vaughan]

Each table should have an key field with an ID number of some sort -- a simple serial number will suffice. This is the field used for relationships.

Whay are the Type fields global? And why is there more than one?

[David Holmberg]

On the stats layout there are 5 fields (type 1-5) which the user types in by himself. For example:

Type 1 - Rent

Type 2 - Amortization

Type 3 - Food

Type 4 - Clothes

Type 5 - Health care

Then 5 "copies" of the summary field from the expense table are placed on the layout, each based on one of the 5 different relationships.

Then I want one field who summarizes all the other costs registered in the expense table (telephone bill, entertainment, furniture and whatever the user chooses to register).

There are 12 records/year in the month table. I want to go from record to record in the month table, viewing the stats, without having to re-type the type 1-5 fields. Thats why they are global.

The questions seems easy to frame when they are in my head, but often gets a bit unclear when I'm trying to type them down. I'm sorry for that.

[QuinTech]

Hi, David. I am going out on a limb because i cannot test this, but i believe the issue is that you are relating a text to a global. If you have an eqaulity join, this relationship would return all records whether they match or not. Thus, if your relationship is Expenses::Type = Stats::Type1, the contents of either field are irrelevant--this relationship is ALWAYS valid.

Now here's the part i don't know because i'm not using v7 yet: Does the above imply that the relationship Expenses::Type != Stats::Type1 (not equal) is always INVALID? I would suspect that is the case. Thus, no matter what you have in Type, Type1, Type2, ... , Type5 you will not get a result.

Hopefully someone using v7 will verify this or set me straight.

Jerry

[David Holmberg]

But how can I work around this?

It has to be quite common that someone wants to get a sum of "the rest".

[RalphL]

I don't see any reason for global fields, but I don't think that is your problem.

Your relationship will have to look something like this:

user = user

year = year

month = month

type != type1

type != type2

type != type3

type != type4

type != type5

Use the Sum function with this relationship to get the total of all others.

[-Queue-]

Happy Birthday, Ralph!

[RalphL]

Thanks!

[David Holmberg]

Earlier I used summary fields and tried to get the stats through relationships. My relationships looked exactly like the one you wrote. Now I tried to use the sum(xxx::xx) fields places in the stats table with the same relationships as earlier. Still doesn't work, nothing gets through the relationships.

If I didn't use global fields, wouldn't I have to type text in each stats record (in the fields 1-5):

[RalphL]

Actually you only need one record in the stats table.

The relationships work for me see the attachment.

[David Holmberg]

Could you attach the file in another compressed format?

[RalphL]

OK here it is zippped.

Expense.fp7.zip