Stuck, need some quick help (long explination)

[ddreese]

I'm having a problem with my relationships (I think) between two tables. I'll try and explain this as simple as possible. Table "CPS Case" is a table to track all the Child Protective Services cased being brought it. Most of the information in this table is brought from other places, like a "client master table", and "contact info" table, etc. One of the tables it's shows information from is the "perp master" table.

On the layout, I list the victim's name and personal information from "client master", as well as the perpetrator information from "perp master". The perps are tracked by a unique id. There can only be one record of them in the perp master. However, a perp can have multiple accusasions against him, so I have an allegation table that also has a field for the perp id, so we can track it by perp.

Where I'm having trouble is, on the main CPS case screen, it lists the client info, and when the case is first openend, the perp information needs to be filled out. However, there could be a perp possibly already in the master list, so I created two buttons: "New Perp" and "Existing Perp". New Perp works fine. Existing Perp takes the user to a layout that shows all the perps (List view), and has an 'OK' button beside their information. The user will click 'OK' if the perp is in there. My script takes the perp id from the list, copys it, and goes back to the main CPS case screen and pastes it into the perp id field there. Now, I thought that the rest of the perp information would automatically come over, because that's how I create a new CPS case in the first place (by copying the id from the client master).

I thought I had my relationships set up right, but apparently not. Am I wrong to wonder why it will do it for the id's from the client master, but not the perp master?

[Razumovsky]

For this to work, your relationship (lets call it "Perp") from CPSCases file to PerpFile should be PerpID::PerpID. Check the "DefineRelationships" menu in the CPSFile and verify that this is so. Next, the Perp Info fields in your CPS main case screen should be fields from a related file. Go to layout mode and check to be sure that each field looks like Perp::FirstName Perp::LastName Perp::Address... the :: indicates that it is displaying info from a related file, and not storing it in the CPSCaseFile.

I suspect that you may be doing it the other way around- that is, storing the Perp Info in the CPS case file, and displaying the related info in the Perp File. PerpFile should hold all the data.

[Ugo DI LUCA]

The perps are tracked by a unique id. There can only be one record of them in the perp master. However, a perp can have multiple accusasions against him, so I have an allegation table that also has a field for the perp id, so we can track it by perp.

Where is this allegation table. In the Perp file. What is it exactly ?

As Razumowsky said, the relationship should be Perp_ID::Perp_ID.

If this relationship is Ok, then you may have done one of these errors.

In layout mode, check what is in the field. It could be either Perp_ID or :Perp_ID.

If it is Perp_ID, then you certainly forgot to define the other records as lookup fields from Perp File, or these lookup fields use another "non valid" relationship.

You could have also used related fields from Perp File.Then Perp ID (CSP File) and ::Adress (for instance) do not relate each other.

If it is ::Perp_ID, then probably the other records are set "inverse" to adress,...

Now, from what suggest Razumowsky (keeping the info from Perp File into Perp File), I think that this could depend on your structure.

I could have one example, and maybe another discussion could take place.

Imagine you make a Condamnation Order for a Perp on January 25th using the adress from the Perp File (related field).

Now imagine this Perp moved to another place and you have to make a Condamnation Order in March, using a new adress.

You understands that the records from the January 25th Condamnation Order will change.

If you have to keep track this kind of records, then you most probably would use a lookup field.

[ddreese]

For this to work, your relationship (lets call it "Perp") from CPSCases file to PerpFile should be PerpID::PerpID. Check the "DefineRelationships" menu in the CPSFile and verify that this is so. Next, the Perp Info fields in your CPS main case screen should be fields from a related file. Go to layout mode and check to be sure that each field looks like Perp::FirstName Perp::LastName Perp::Address... the : indicates that it is displaying info from a related file, and not storing it in the CPSCaseFile.

I suspect that you may be doing it the other way around- that is, storing the Perp Info in the CPS case file, and displaying the related info in the Perp File. PerpFile should hold all the data.

Thanks for the reply. The relationship is indeed perp id::perp id. The fields that are on the layout are ::perp first name, ::perp last name, etc. So that doesn't seem to be it. The Perpfile is indeed holding all the information, the CPS table only has about 4 fields or so in it.

[ddreese]

Thank you for your reply. The allegation table is a seperate file from perp master and the cps table. Basically what it is, is a collection of fields of what the "allegations" are based on that case. It's basically a bunch of check boxes to describe what "allegations" are pending for the case. For instance, a cps case gets open, the perp name is "John Smith" for this particular case, John has an allegation record that tracks what he's done to the child i.e. Burns/Scalding, POisoning, Sexual Abuse, Malnutrition... There are about 50 different checkboxes. I have a seperate allegation table because one perp may have more than one case opened against him, and it's just easy too keep all the allegations in it's own file. Hope that helps a little bit.

What gets me is that it works fine for creating a new perp, but it gives me errors when I'm just copying the perp id over. I think it's taking that as me trying to create the perp that way (And I can't because the ID is unique), when I really just want the info from it.

[Ugo DI LUCA]

Did you defined one or more relationship from Case to Perp File (eg NAMEtoNAME and IDtoID.

So your fields in the Layout could have ::perp first name, ::perp lastname,... (related fields) but using another relationship that the one used for ::PerpID.

Another question. Does your script succeed in the "paste" in ::PerpID ? Or is it that you cannot enter the field ?

Last. Do you in case use (or used) another file called PerpFile (a clone or backup file) where FM is actually looking for the relationship in an another file called PerpFile (without record in it for the Perp_ID you've just pasted) ? Check the relationship again, and the file at right side of relationship.

[ddreese]

Did you defined one or more relationship from Case to Perp File (eg NAMEtoNAME and IDtoID.

So your fields in the Layout could have ::perp first name, ::perp lastname,... (related fields) but using another relationship that the one used for ::PerpID.

Another question. Does your script succeed in the "paste" in ::PerpID ? Or is it that you cannot enter the field ?

Last. Do you in case use (or used) another file called PerpFile (a clone or backup file) where FM is actually looking for the relationship in an another file called PerpFile (without record in it for the Perp_ID you've just pasted) ? Check the relationship again, and the file at right side of relationship.

Aha! You cracked it! I did indeed have more than one relationship to that file (which wasn't really needed anyway as far as I know). I didn't know that more than one relationship between tables could muck something like that up. Thank you very much.

[Ugo DI LUCA]

Oh. Glad it worked !

I remember spending almost one day on the same problem. I sweared that I would always delete unused relationships.

But do consider the lookup field option.