Relationships between 2 data Bases and Portals

[Joseph31]

Hey...Everyone..

Here is the situation...

I have two files (Data Bases) one has insured information in it (like name, address, broker) and the other file has Policy Information in it (like Policy number, amount, Insurance company etc...)

I need to make a portal on the first data base that shows all of the policies relating to that insured...Only that Insured... But wait... I also need a add policy script so I can add a new policy to only that insured...Please Help

[QuinTech]

Hi Joseph, i'm stalking you from thread to thread.

You don't necessarily need a script, as you can just set the relationship to allow creation of new records. Then, from a client's record, just tab into the portal and enter a new policy into the first blank portal row.

This will create a new record in policies with a ClientID matching the ClientID of the record you are currently in. If there are other fields that need to be set in Policies, you may or may not need a script.

J

[Joseph31]

wow we are becoming old friends...See you in the next thread

What I would Like to do is make a script that makes a window (second data base) pop up with a new record so I can fill out all of the fields...But here is the thing I need it to be associated with the insured that I am on...So when I return to the first data base I will see it in the portal...(Seeing only the information I want in the portal but have the ability to click on the portal line and bring up the other window with lot of info)..Can you help me do this

[bruceR]

Why make a new window? You could do that but it's not necesary. See the response and example file in your original thread.

[QuinTech]

Bruce is right, Joseph, it's not necessary. But if you want to do it for design purposes, here's one thing i do:

At layout 1, you have a button that begins the process of adding a policy to this client. This button does two things: 1. Sets a global field called "CurrentClientID" to the ClientID of the record you're working in. 2. Goes to a layout which is an exact duplicate of layout 1, except that it shows two global fields: GlobalPolicyName and GlobalPolicyBeginDate (obviously you'll add more fields to reflect all the data you want to track on your policies).

So the user goes to layout 2 and enters policy information. They then click the "Continue" button which runs the script you have written called "Create New Policy". This script has exactly one step, namely, to Perform Script [ External ] where the external script it performs is "Create New Policy" IN THE FILE Policies.fp5.

Here's where it gets fun. In both Clients.fp5 and Policies.fp5, you have a field called U1 that is always equal to 1 (make it a calc field, let's say). In Policies, there is a relationship from this field to the corresponding field in Clients. Now Policies.fp5 has the ability to access global fields from Clients. Using this relationship, your script "Create New Policy" in Policies.fp5 does this:

1. create new record

2. set ClientID = Relationship::CurrentClientID

3. set PolicyName = Relationship::GlobalPolicyName

4. set PolicyBeginDate = Relationship::GlobalPolicyBeginDate

5. return to layout 1 in Clients.fp5

I'll note that you don't need a second layout here if you use the hidden portal trick; but i'll leave that one for another day.

[Joseph31]

the reason I thought I need another page (Layout) is that I have about 50 Fields That I need to fill out per policy Number...There can be many Policy Numbers per insured... Should I put the Policy Information in a totaly seperate database?

Now I have read your message to me several times... Is there two scripts...or one.. Also why this Calc field? What does that do...

Thank you so much....For you help...

[bruceR]

Yes, each policy should most definitely be a separate record in a separate database. Did you look at the example? There are a variety of ways to handle the 50 field issue. But are you really sure you need 50 fields? Example?

The policy data be entered/viewed from the Client file. Basically in Client you have a field SelectedPolicyID and relate that to the policyID. Then a button in the portal sets this field.

[Joseph31]

where is the example..I am sorry if i missed that.... The 50 fields are things like amount, bid amount, purchase price, net pruchase price, Gross purchase price, Profit, Insurance company, address, etc... Is there something I could see like a file...

if not...can you seperate the two scripts so I can see them more clearly...I understand what it is doing but I have no Idea how...

[bruceR]

"where is the example..I am sorry if i missed that"

Then read the message again.

[Joseph31]

I saw the word example...is thought you meant there was a download....sorry

[QuinTech]

Hi Joseph, there are two scripts, one in Clients, the other in Policies. The only function of the script in Clients is to call the script in Policies, since an FM6 file cannot call a script in another file.

The calculation is so that you have a field that is always equal to 1. When you set a relationship to this field from the global field in Policies, that allows the Policies file to view data that lives in globals in Clients.

Whereas a typical relationship allows you to view one (or several) record(s) from a child file that match certain limited criteria, the "Universal" relationship i described in the previous post allows you to view ALL records from a child file at all times.

[bruceR]

I did mean there was a download, I told you exactly where it was.

READ THE MESSAGE! HERE IS WHAT IT SAID:

>>>> See the response and example file in your original thread<<<<

[QuinTech]

I think maybe this is what Bruce means.

[bruceR]

Yes, that is the thread, and the last message in the thread includes the example.