Related Key in a calculated define field

[Munchie]

Currently, I have a layout that's called Summary. It has a bunch of number fields, that get updated when a user presses the button to goto that layout. The script fills in the fields each time a user goes to the layout.

For example, to get the first field, EX Total Accounts, it sets the field

"EX Found Key" to "2", and then counts how many records are in the relationship, which is EX Relation Key,and sets EX Total Accounts. Next, it changes the "EX Found Key" to "1" and counts them again, and sets the next field, EX Accounts New.

I want to be able to make EX Total Accounts a calculation field, however, I haven't figured out how to tell it to only count the matching key 1, not 2.

I don't think I've explained everything well enough, so I'm including some of the code below:

EX Relation Key 1 = Social Securtiy & " " & EX Found Key 1

The script that fills in the fields looks sorta like this:

Set Field [EX Found Key 1, 2]

Set Field [EX.Accts Total, Count(Accts EX 1::Related Key 1)]

I hope somebody out there understands what I am attempting to do.

Thanks!

Munchie

[BobWeaver]

If you only have two possible values for the key field--1 and 2-- then you can create two globals gOne and gTwo (with the values 1 and 2) and two relationships using these fields on left side of the relationship. Then, define each calculated field to use the corresponding relationship.

[Munchie]

I thought of that, however, there would have to be 27 unique relationships to do this. I only gave one example. There are 27 total fields I would like to do this with.

Thanks for the quick response!

[BobWeaver]

Well it wouldn't be unheard of to have a relationship for each one. I've created files with more relationships than that. Alternatively you could have a single relationship, and 27 calculated fields in the related file.

The fields would be of the form:

cField1:

(Related Key 1 = 1)

cField2:

(Related Key 1 = 2)

cField3:

(Related Key 1 = 3)

etc., and then in your main file calculated field the formula would be:

EX.Accts Total 1:

Sum(Accts EX 1::cField1)

EX.Accts Total 2:

Sum(Accts EX 1::cField2)

etc.

[Munchie]

Bob,

27 calc fields would work, however, I don't understand how to make it work.

The main contact database is where the fields I want to see a summary of each type of account. Currently, the relationship is SS &" "&EX Found Key 1

Found key 1 is a calculation that determines the type of account it is. New accounts are 1, late accounts are 2,ect... The relationship is then SS 1 for new accounts, SS 2 for late accounts. I use this in a portal in the main database to view types of accounts for each client. What I want to do now is have a layout that has a summary for that client about each type of account.

What is worse, is the accounts are seperated into 3 different files.

27 relationships is bad enough, however, I have to have 9 new fields in each of the 4 databases to match up with.

This cries of bad database design to me.

Thanks!

[Munchie]

A crazy thought hit me. Is it possible to have a single relationship that would show me all the records in the other databases that have a matching SS #, and then filter them down based on what Found Key they have? Maybe some kind of self join?

[BobWeaver]

You can have a relationship that shows all records from another file but it likely won't help. This is called a constant relationship. Create a calculated field in each file that always has the value 1, and then use this field for the relationship. This will show all records in the related file. The problem is that you can't now use this related data in a self-join in the main file because it can't be indexed. But you can use this relationship to summarize data that has been pre-filtered in the related file. Essentially, that is what I was trying to explain in my last post about using calculated fields in the related file.

Why do you have the accounts in 3 different files? This makes things incredibly awkward to deal with in database terms.

To explain a bit more about the calculated fields in the related files, you need to create one calc field for each different account type. The field returns a result of 1 (true) if the account is that type, and 0 (false) otherwise. So for account type one, the calculation formula would be in the form:

Calc field "IsType1":

(Type = 1)

for account type 2, Calc field "IsType2" would be:

IsType2 = (Type = 2)

and so on.

In your main file you would also have a calculated field for each different account type.

So the formula for summary field "SummaryType1" for account type 1 would be:

Sum(WhatEverRel::IsType1)

The formula for summary field "SummaryType2" for account type 1 would be:

Sum(WhatEverRel::IsType2)

As for the exact relationship you need to use, I'm not really sure. You said you were using SS &" "&EX Found Key 1 for the key field. But, you didn't explain what SS is supposed to be. Is it another field, or is it just the text "SS" ?

Also, you didn't mention whether this field is in the parent file or the related file.

<Edit note: said "field" when I should have said "file">

[Munchie]

This database is for tracking clients Credit. EX would be Experian. SS is social security, which is the way we identify each client. There are three reporting agencies that we track, that is the reason for the 3 related databases.

The parent database is the file that our employees use to view each clients digital credit report. When working with records, we use a portal to view each account.

The parent database is where we want to view the summaries. Each of the related files would have 75 or so records in each that relate to a single client.

When talking to a client, we are updating them about how their credit looks. "You have a total of 15 accounts on Experian, 4 of them have lates, 3 are new, and 6 have been deleted since we began." would be the type of conversation I would have with a client. I don't want to have to work to get that data. I'm getting it right now by a script changing the multi-key, and then setting a field with the number of matching records. It takes about 30 seconds to do 27 different fields, from 3 related databases. I want them to be calculated, and available when I talk to a client, or when I want to run a script that takes the data, and emails it to a client.

Hope this clarifys what I'm doing.

I think I understand what you are saying now about the Sum(WhatEverRel::IsType1). I'm going to see if that works for me now.

Any other insight would be helpful too.

Thanks!

[Munchie]

<Munchie does the happy dance!>

*******! That was easy. Thank you SOOO much for the help!