f



Relationships within databases

This is my problem,,,

I have a clinet database which links to other databases:

Client database - Main
Phone numbers
Activity notes
Mortgages

I have clients who build a proerty portfolio's the issue is that some of 
these properties are owned just in their name, some are owned in joint names 
name and some are owned as a group of 3 individuals.

I have the Mortgage database showing as a Portal, currently linked by a 
unique ID that is referenced to the main database.

What I want to be able to achieve is a way of showing only mortgages 
relating to the clients they relate to. I'm confused how I can show this at 
present.

At present I have 3 records for 3 different clients, each client has several 
mortgages, some on their own, some with one of the other clients and some 
with both of the other clients.

How can I build something where I'm not entering data twice?

I know it's confusing, I'm confused just trying to describe it.

PS Using FM 6

Ernie 


0
Falcon
1/12/2005 5:25:56 PM
comp.databases.filemaker 11052 articles. 0 followers. amosw01 (46) is leader. Post Follow

20 Replies
603 Views

Similar Articles

[PageSpeed] 44

In article <E4dFd.71836$C8.28058@fe3.news.blueyonder.co.uk> Falcon,
sorry@ihatespam.com writes:
>What I want to be able to achieve is a way of showing only mortgages 
>relating to the clients they relate to. I'm confused how I can show this at 
>present.

Ernie, I'm still learning too, and I'm sure there are different ways of
doing this, but I think the basic setup could be as follows...
In your client database file each client should have a unique 'Client id'.
Then in your mortgages database file you need to add a 'client id' field.
This is the only bit of data you need to duplicate.
Then set up a relationship from your client file to mortgage file using
Client Id as the key field.
Then in your chosen layout in your client file you can add a portal
showing the mortgage file records. Whatever client you're looking at,
their mortgages will appear in the portal.

I'm not sure of the best way to cope with clients working together. I
wonder if each group of clients working together should get their own
Client Id.
Alternatively perhaps the client id field in mortgages file could be set
to a repeating field so it could hold more than one client?

Gary
0
Ovalking
1/12/2005 10:20:53 PM
Thanks I have done all of that it is the stage that you describe below that 
has me perplexed!

Thanks for the suggestions however.

Ernie
>
> I'm not sure of the best way to cope with clients working together. I
> wonder if each group of clients working together should get their own
> Client Id.
> Alternatively perhaps the client id field in mortgages file could be set
> to a repeating field so it could hold more than one client?
>
> Gary 


0
Falcon
1/12/2005 10:40:24 PM
Falcon wrote:
> This is my problem,,,
> 
> I have a clinet database which links to other databases:
> 
> Client database - Main
> Phone numbers
> Activity notes
> Mortgages
> 
> I have clients who build a proerty portfolio's the issue is that some of 
> these properties are owned just in their name, some are owned in joint names 
> name and some are owned as a group of 3 individuals.
> 
> I have the Mortgage database showing as a Portal, currently linked by a 
> unique ID that is referenced to the main database.
> 
> What I want to be able to achieve is a way of showing only mortgages 
> relating to the clients they relate to. I'm confused how I can show this at 
> present.
> 
> At present I have 3 records for 3 different clients, each client has several 
> mortgages, some on their own, some with one of the other clients and some 
> with both of the other clients.
> 
> How can I build something where I'm not entering data twice?
> 
> I know it's confusing, I'm confused just trying to describe it.
> 
> PS Using FM 6
> 
> Ernie 
> 
> 

you need an additional table: Mortgage Clients, fields Motrgage_ID and 
Client_ID
create a rel from Mortgage to Mortgage Clients using Mortgage_ID
and enter the clients (Client_ID) in a portal... so you can have one or 
unlimited clients per mortgage
i.e. you need to pass the Client ID into the Mortgage Client

then create a rel from Clients to Mortgage Client using Client_ID, and 
you can use a portal to show all the client mortgages


Chris Brown
Neurosurgery
university of Adelaide






0
Chris
1/12/2005 11:21:44 PM
Thanks Chris, will try and give that a go, if I can get my head around 
that!!!

Ernie 


0
Falcon
1/13/2005 7:52:41 AM
Chris can you help me understand this better please?

you need an additional table: Mortgage Clients, fields Motrgage_ID and
> Client_ID -

ok done.

>create a rel from Mortgage to Mortgage Clients using Mortgage_ID

Ok here!

> > and enter the clients (Client_ID) in a portal... so you can have one or
> unlimited clients per mortgage

So where is this portal? - Client datbase or Mortgage database?

> i.e. you need to pass the Client ID into the Mortgage Client - how is the 
> client ID created, I can't see the way this works on paper or in my head 
> so I'm strugling with FM side as you can see. I'm sure it can be done 
> however!

Thanks Chris
>
> then create a rel from Clients to Mortgage Client using Client_ID, and you 
> can use a portal to show all the client mortgages
>
>
> Chris Brown
> Neurosurgery
> university of Adelaide
>
>
>
>
>
> 


0
Falcon
1/13/2005 8:48:33 AM
In article <BBqFd.137505$Z7.101788@fe2.news.blueyonder.co.uk> Falcon,
sorry@ihatespam.com writes:
>So where is this portal? - Client datbase or Mortgage database?

Ernie, I think Chris means putting a portal in a layout in the Mortgage
database. Then drag the field Mortgage Clients::Client Id into the portal.
When you create the relationship from Mortgage database to Mortgage
Clients database check the box to allow creation of records. Then you
just add clients to a mortgage by adding their client Id into the portal.
The thing I don't know is how to show the other client type fields in the
portal (in addition to their Id).

Gary
0
Ovalking
1/13/2005 8:00:15 PM
Ovalking wrote:
> In article <BBqFd.137505$Z7.101788@fe2.news.blueyonder.co.uk> Falcon,
> sorry@ihatespam.com writes:
> 
>>So where is this portal? - Client datbase or Mortgage database?
> 
> 
> Ernie, I think Chris means putting a portal in a layout in the Mortgage
> database. Then drag the field Mortgage Clients::Client Id into the portal.
> When you create the relationship from Mortgage database to Mortgage
> Clients database check the box to allow creation of records. Then you
> just add clients to a mortgage by adding their client Id into the portal.
> The thing I don't know is how to show the other client type fields in the
> portal (in addition to their Id).
> 
> Gary


as  Ovalking indicated:
a portal in Mortgages, using a  Mortgage_ID::Mortgage_ID rel to 
Mortagage Clients, will show the clients for the current Mortgage record.

If that rel is set for allow create new, then the Client_ID can be typed 
into the last portal (blank) row, to create a record. There are other ways.

First though you need a list of all clients (or you could of course 
navigate to Clients db). You could show this list by a portal in 
mortages using a constant rel (1:1) from mortagges to clients. Switching 
to a dedicated  layout might be warranted re screen real estate. Instead 
of a constant rel a 'filtered' rel could be created  by using  a global 
letter (scripted in) to _first letter of client surname  calc. It 
depends on how many clients, and how necessary it is to avoid excessive 
scrolling... Whichever way, grab the Client_ID from the portal row, and 
either script it across to the Mortagage Clients db, or type it in the 
Mortagage Clients portal as above.


If you want to script it, as you are in FM6 , you will need to create  a 
key_NewMortagageClients field (global) in Mortagages. and create an acrr 
rel to Mortagage Clients. The key will hold the Mortgage_ID and 
Client_ID values...
e.g M0235 C0748
get back if more detail required

Gary also wrote:
 > The thing I don't know is how to show the other client type fields in the
 > portal (in addition to their Id).

If you were using FM7 this is very easy: just create a (hopped) rel from 
MortagageClients to Clients using Client_ID, and put the hopped 
MortagageClients fields in the portal. In FM6, you will need to create a 
rel from MortagageClients to Clients using Client_ID, and then a calc 
field(s) in MortagageClients to return the related data; a join file 
approach, tehn add those calc fields to your portal.





regards

Chris

0
Chris
1/14/2005 1:57:04 AM
Falcon wrote:
> Chris can you help me understand this better please?
> 
> you need an additional table: Mortgage Clients, fields Motrgage_ID and
> 
>>Client_ID -
> 
> 
> ok done.
> 
> 
>>create a rel from Mortgage to Mortgage Clients using Mortgage_ID
> 
> 
> Ok here!
> 
> 
>>>and enter the clients (Client_ID) in a portal... so you can have one or
>>
>>unlimited clients per mortgage
> 
> 
> So where is this portal? - Client datbase or Mortgage database?
> 
> 
>>i.e. you need to pass the Client ID into the Mortgage Client - how is the 
>>client ID created, I can't see the way this works on paper or in my head 
>>so I'm strugling with FM side as you can see. I'm sure it can be done 
>>however!
> 

see reply to Ovalking re the where it is


As for how the Client_ID is created, you need a Client_ID field in 
Clients. Create a text field and set the options to auto enter serial 
value C00001 with 1 increment. Make it's validations unique and not 
empty. This way each time you create a new client (record in Clients), 
the value will be created. As you already have clients, you will need to 
use Replace command to enter the series. Go to a list or table view, 
show all records, click in the ID field, and select Records/Replace 
field contents. Type your starting value in the appropriate box (e.g 
C00001) and replace. check the Update ... box . For future reference, be 
careful when using Rep-lace, it can't be 'undone'. Create and practice 
on a temporary field if you have to. With blank data it doesn't matter


As a rule of thumb, always use unique serial ID rather than names... for 
data tables.  ID's avoid duplications (Jo Smith,  John Smith...) and 
spelling/entry errors... smaller, faster... a host of reasons.


regards

Chris


0
Chris
1/14/2005 2:09:01 AM
Ok, I'm wondering if I have explained myself very clearly and if what I'm 
trying to do is possible!

3 Files

Clients, Mortgages & Mortgage Client

Clients: - Name, address, phone numbers etc.....
Mortgages:- Lender, amount of loan, valuation, purchase price etc....
Mortgage Clients:- who is part to one of the mortgage above.

Fields used here.

Clients:- Client ID (Using serail value C000001 and set to auto increment)
Mortgages:-  Mort ID
Mort Clients:- Mort ID & Client ID

Relationships
Mortgages & Mortgage Client: Using Mort ID
Clients & Mort Clients: Using Client ID

Portals
Portal in Mortgages allowing data entry into Mortgage Clients
Portal in Clients allowing data to be shown in Mort Clients

Needs: I want to be able to enter data through the portal from Clients, into 
Mortgages / Mortgage Clients.

PROBLEMS

1) From clients I am able to see very little data of use, the available 
fields to select are only Mort ID & Client ID. (Client ID is a numeric 
number eg C000001 and Mort ID is set to numeric eg 23
2) If I go direct to Mortgages,  the fields become locked and won't allow 
data entry.

What I Did!

Creating the portals is not a problem.....I'm used to using portals.

I did set up some additional fields within Mort Clients to extract the data 
required from Mortgages by way of a calculation. This worked fine except I 
could not amend or insert any data because the fields I used were 
calculations!

Any suggestions appreciated!

Ernie


0
Falcon
1/14/2005 12:54:08 PM
Judging by your record-locking problems between windows, I'm guessing 
you must be in 7.  If you are in a record in one window (say, in a 
portal row to mortgages), and then you go to another window (Mortgages 
itself), then the Mortgage record will be locked.

To help with your problem #1.  Your portal is correct from Clients to 
MortClients.  But to put useful data in the portal, the fields you want 
to put in the portal should be from the Mortgages table.



Falcon wrote:
> Ok, I'm wondering if I have explained myself very clearly and if what I'm 
> trying to do is possible!
> 
> 3 Files
> 
> Clients, Mortgages & Mortgage Client
> 
> Clients: - Name, address, phone numbers etc.....
> Mortgages:- Lender, amount of loan, valuation, purchase price etc....
> Mortgage Clients:- who is part to one of the mortgage above.
> 
> Fields used here.
> 
> Clients:- Client ID (Using serail value C000001 and set to auto increment)
> Mortgages:-  Mort ID
> Mort Clients:- Mort ID & Client ID
> 
> Relationships
> Mortgages & Mortgage Client: Using Mort ID
> Clients & Mort Clients: Using Client ID
> 
> Portals
> Portal in Mortgages allowing data entry into Mortgage Clients
> Portal in Clients allowing data to be shown in Mort Clients
> 
> Needs: I want to be able to enter data through the portal from Clients, into 
> Mortgages / Mortgage Clients.
> 
> PROBLEMS
> 
> 1) From clients I am able to see very little data of use, the available 
> fields to select are only Mort ID & Client ID. (Client ID is a numeric 
> number eg C000001 and Mort ID is set to numeric eg 23
> 2) If I go direct to Mortgages,  the fields become locked and won't allow 
> data entry.
> 
> What I Did!
> 
> Creating the portals is not a problem.....I'm used to using portals.
> 
> I did set up some additional fields within Mort Clients to extract the data 
> required from Mortgages by way of a calculation. This worked fine except I 
> could not amend or insert any data because the fields I used were 
> calculations!
> 
> Any suggestions appreciated!
> 
> Ernie
> 
> 

-- 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg              (818) 883-2846
FM Pro Solutions       Los Angeles, California

FileMaker 7 Certified Developer
Associate Member, FileMaker Solutions Alliance
0
Howard
1/14/2005 6:01:38 PM
Howard, I'm using FM6

How can you get fields from Mortgages to show in the portal window. Unless a 
relationship is developed it won't allow this!

Or am I wrong?

If a relationship is required, how do you do this because a mortgage might 
relate to one or more clients?

Ernie 


0
Falcon
1/14/2005 6:17:30 PM
I was assuming you were in FM7, where you can put fields from another 
table down the relationship chain into a portal somewhere else in the 
relationship chain.

For 6, you need to create a set of unstored calc fields in the 
MortClients file to give you what you want to see in the portal.  So, 
for example, create a Lender calc field in the MortClients file that has 
a text result, is unstored and points to the Lender field in the 
Mortgages file.

with this, you have a portal on your Clients layouts that points to 
MortClients, and you can now put that Lender calc field (from 
MortClients) in the portal.


Falcon wrote:
> Howard, I'm using FM6
> 
> How can you get fields from Mortgages to show in the portal window. Unless a 
> relationship is developed it won't allow this!
> 
> Or am I wrong?
> 
> If a relationship is required, how do you do this because a mortgage might 
> relate to one or more clients?
> 
> Ernie 
> 
> 

-- 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg              (818) 883-2846
FM Pro Solutions       Los Angeles, California

FileMaker 7 Certified Developer
Associate Member, FileMaker Solutions Alliance
0
Howard
1/14/2005 6:32:47 PM
I have done that Howard, but again am I right in thinking that becasue it's 
a calc field, I can't enter data via the portal?


"Howard Schlossberg" <howard@antispahm.fmprosolutions.com> wrote in message 
news:10ug42gisouthef@corp.supernews.com...
>I was assuming you were in FM7, where you can put fields from another table 
>down the relationship chain into a portal somewhere else in the 
>relationship chain.
>
> For 6, you need to create a set of unstored calc fields in the MortClients 
> file to give you what you want to see in the portal.  So, for example, 
> create a Lender calc field in the MortClients file that has a text result, 
> is unstored and points to the Lender field in the Mortgages file.
>
> with this, you have a portal on your Clients layouts that points to 
> MortClients, and you can now put that Lender calc field (from MortClients) 
> in the portal.
>
>
> Falcon wrote:
>> Howard, I'm using FM6
>>
>> How can you get fields from Mortgages to show in the portal window. 
>> Unless a relationship is developed it won't allow this!
>>
>> Or am I wrong?
>>
>> If a relationship is required, how do you do this because a mortgage 
>> might relate to one or more clients?
>>
>> Ernie
>
> -- 
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Howard Schlossberg              (818) 883-2846
> FM Pro Solutions       Los Angeles, California
>
> FileMaker 7 Certified Developer
> Associate Member, FileMaker Solutions Alliance 


0
Falcon
1/14/2005 7:59:16 PM
Ahhh -- correct you are.  Best you might do is to go to the related 
MortClients record where you have a layout with the related Mortgage 
fields and allwo the data entry from there.  Click a 'done' button to go 
back to the Client record.

Falcon wrote:
> I have done that Howard, but again am I right in thinking that becasue it's 
> a calc field, I can't enter data via the portal?
> 
> 
> "Howard Schlossberg" <howard@antispahm.fmprosolutions.com> wrote in message 
> news:10ug42gisouthef@corp.supernews.com...
> 
>>I was assuming you were in FM7, where you can put fields from another table 
>>down the relationship chain into a portal somewhere else in the 
>>relationship chain.
>>
>>For 6, you need to create a set of unstored calc fields in the MortClients 
>>file to give you what you want to see in the portal.  So, for example, 
>>create a Lender calc field in the MortClients file that has a text result, 
>>is unstored and points to the Lender field in the Mortgages file.
>>
>>with this, you have a portal on your Clients layouts that points to 
>>MortClients, and you can now put that Lender calc field (from MortClients) 
>>in the portal.
>>
>>
>>Falcon wrote:
>>
>>>Howard, I'm using FM6
>>>
>>>How can you get fields from Mortgages to show in the portal window. 
>>>Unless a relationship is developed it won't allow this!
>>>
>>>Or am I wrong?
>>>
>>>If a relationship is required, how do you do this because a mortgage 
>>>might relate to one or more clients?
>>>
>>>Ernie
>>
>>-- 
>>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>>Howard Schlossberg              (818) 883-2846
>>FM Pro Solutions       Los Angeles, California
>>
>>FileMaker 7 Certified Developer
>>Associate Member, FileMaker Solutions Alliance 
> 
> 
> 

-- 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg              (818) 883-2846
FM Pro Solutions       Los Angeles, California

FileMaker 7 Certified Developer
Associate Member, FileMaker Solutions Alliance
0
Howard
1/14/2005 8:14:54 PM
Ok, I have the portal showing the correct info for each client!

I can't edit data in the portal directly, so I have created a button to 
create new mortgages!

I am trying to create a button now that will take me back to the Mortgage so 
I can edit / change the data!
I can use goto related record because their is no relationship between:

Clients & Mortgages = (each mortgage can belong to several people)

How do I create a script to identify the right mortgage and take me to that 
page?

Thanks

Ernie 


0
Falcon
1/15/2005 11:43:40 AM
In article <Ml7Gd.137829$48.17406@fe1.news.blueyonder.co.uk> Falcon,
sorry@ihatespam.com writes:
>How do I create a script to identify the right mortgage and take me to that 
>page?

How about something like:
Copy Mortgage Id
Switch to Mortgages
Enter Find mode
Paste Mortgage Id
Perform Find

?
0
Ovalking
1/15/2005 2:20:52 PM
 From CLients to Mortgages, the quickest way would be:

Set Field [gMortgageID; MortClients::MortgageID]
GTRR [Mortgages_by_Single_MortgageID]





Falcon wrote:
> Ok, I have the portal showing the correct info for each client!
> 
> I can't edit data in the portal directly, so I have created a button to 
> create new mortgages!
> 
> I am trying to create a button now that will take me back to the Mortgage so 
> I can edit / change the data!
> I can use goto related record because their is no relationship between:
> 
> Clients & Mortgages = (each mortgage can belong to several people)
> 
> How do I create a script to identify the right mortgage and take me to that 
> page?
> 
> Thanks
> 
> Ernie 
> 
> 

-- 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg              (818) 883-2846
FM Pro Solutions       Los Angeles, California

FileMaker 7 Certified Developer
Associate Member, FileMaker Solutions Alliance
0
Howard
1/15/2005 6:28:44 PM
Howard I don't recognise some of this?

gMortgageID - What is the g, it's not a field I have. Should I create a 
Field gMortgageID?

GTRR ??? What does this do?

Mortgages_by_Single_MortgageID - Is this another field?


Set Field [gMortgageID; MortClients::MortgageID]
> GTRR [Mortgages_by_Single_MortgageID]


0
Falcon
1/15/2005 10:46:35 PM
Sorry for the shorthand--

> gMortgageID - What is the g, it's not a field I have. Should I create a 
> Field gMortgageID?

The g indicates that it is a global field, something you'll need to 
create if you don't already have a global field to use.


> GTRR ??? What does this do?

This is an abreviation for thr 'go to related records' script step.


> Mortgages_by_Single_MortgageID - Is this another field?

This is a new relationship from the gMortgageID field to the MortgageID 
in the Mortgages file.


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg              (818) 883-2846
FM Pro Solutions       Los Angeles, California

FileMaker 7 Certified Developer
Associate Member, FileMaker Solutions Alliance
0
Howard
1/15/2005 11:04:43 PM
Thanks everyone, got there in the end!

Ernie 


0
Falcon
1/16/2005 8:30:43 AM
Reply: