Relationships and global fields..

[macaroni]

I often find myself using alot of global fields which I really hate doing.

I have a Client DB, Proposal DB and Invoice DB. The Client DB is a 1 to many with the Proposal DB related by a clientID. The Proposal DB is related to the InvoiceDB by a Proposal ID.

When I want to create a report to print out in a layout in the Invoice DB, I find myself creating a global field in the Client DB and relating that to the proposalID and then creating a bunch of global fields in the Invoice DB which are set to the values of the fields in the Client DB in the script.

The global fields are for the first name, last name, email, phone, title,

company name, and a lot of the fields.

Is there a better more concisely designed way of putting the information from the Client DB in the Invoice DB layout.?

[Pupiweb]

Sure

You need to "tunnel" data to the Invoices.fp5 file

Briefly:

Make a ClientID field in Invoices.fp5, let it be filled by Lookup, using the relationship to Proposal.fp5 based on ProposalID

Then use ClientID in Invoices.fp5 to lookup client data (ClientName, Address etc ...) using a relationship to ClientID in Clients.fp5

Alternatively you might like to lookup the client data in Proposals.fp5 and then from there to Invoices.fp5 ... up to you

[Ugo DI LUCA]

Hi Macaroni and Pupiweb,

It appears me strange that the relationship from Proposal to Invoice is the Proposal_ID. May this be possible that when your business evolves, you could have a one to many relationship from Invoice to Proposals ?

For instance, one Proposal may be splitted into 2 different invoices, or 2 proposals may be joined into one single invoice....

If so, you should think of another way to pull the Client_ID into the Invoice File, using a Line Item, with a combination of Pupiweb lookups and global fields.

If not, Pupiweb is right. Tunnelling is the way you should do this.

[macaroni]

Sorry I'm a little confused on tunnelling. Could you hopefully explain it in a more general sense.

How does it differ from a portal. I take that a portal i used when I want to display information of directly related records. For example, if I had 2 Databases, one agentDB and another customerDB where an agent can have many customers but no customer can have different agents. So if I wanted to display the information of the customers an agent has in a layout in the agent db I use a portal.

However If i wanted to display information about an agent in a layout in the customer db I use a tunnel.

Please correct me if I'm wrong....since I'm still trying to grasp this whole issue of portals, relationships , lookups and now tunnelling...

[Tamarin2087]

I think I can generalize what Pupiweb is referring to. Feel free to correct me if I misunderstood.

The intent is to get information in File A to File C smoothly when you have three files. File A is related to File B which in turn is related to File C by a second field. So A and C have no relationship.

Tunneling would create a field in File C that can be related to a field in File A. Populate that field using a Lookup from the relationship that File C already has with File B. Then you will have a field that can be used to create an C to A relationship.

If my thinking is straight,

Clients.fp5 = File A

Proposals.fp5 = File B

Invoices.fp5 = File C

[macaroni]

Tamarin,

To test out what you just said, I created 3 Test files,

Agent.fp5 = File A

Customer.fp5 = File B

Order.fp5 = File C

File B is related to File A through the agent_id. File C is related to File B by the customer_id.

I created a field in File C called: agent_id and made it a lookup based on the field relationship with File B of customer ID.

I created test scripts that creates an agent in File A and then calls a script in File B to create a customer and then calls a script in File C to create an order.

As I ran through the script the agent_id and email fields that were File C (based on tunnerlling) in fact were filled in. (I was very happy to see this work

However I do have one more question, if I change the any information in the Agent (File A) such as their agent_id or email,

how will this reflect in both the Customer and Order Files?

All of you have been a big help and you don't know how ecstatic I was when I was able to sort it out....(well partially anyways)

Thanks.

[Ugo DI LUCA]

Hi Macaroni,

Happy you find the tunnel...

You wouldn't change the ID when entered Hopefully, it wouldn't reflect in the Main file anyway.

This change must be done in the Agent file.

You could change the email or any other datas, but this won't update the Agent file.

Think of that:

When an Invoice is processed for a customer with a specific adress, all records in this invoice is "locked", even the adress. If your customer change adress, there is no reason for the invoice to change from the original printed and archieved one. Am I right ?

[Tamarin2087]

When I have information change in a relational setup (for instance, an agent's supervisor is changed) there are 2 methods I use to update the other files.

1) If the value in the file does not need to be indexed, I create a calculation field that updates when any changes occur in the agent data.

2) If the value DOES need to be indexed, I run a script to ReLookup the information. This can be triggered either from a button or from a change in the source field (I use ScriptIT for this). It can be a bit cumbersome if the file doing the ReLookup is large, but you could easily script it so that it only did the lookup on a found set of records for that individual.

As dilucaugo68 pointed out though, whether you update or not depends on the nature of what you are storing. If you want to look at an old invoice and see where it was shipped to, you don't want to dynamically update the addresses in that file. However, if you are keeping a database of current email, address, account info, etc then the updates will need to be done.

[Ugo DI LUCA]

Hi Tamarin, Hi Macaroni,

I would make the Agent_ID field a whole button that would lead you to the correct record in Agent file for you to update this record.

Re-lookup could be sometimes dangerous. If you really need to re-lookup the field, you should look for a way to a "conditional look-up key".

[macaroni]

Thank you all so much.

To summarize, the data entered in the lookup field is persistent only when a relookup is called again based on the relationship that is used to create the lookup.

If the data is changed in the Agent DB such as their phone number, this change won't relfect on the Order record since the lookup copies only the data based on the time it was lookup-ed.

Am I correct?

Is there a way to make updating related fields or lookup fields upate automatically(i.e. when the original field is changed the change is propogated through out the related fields. I guess for a lookup, no since a relookup call is needed but if the field is not a lookup how is this field updated?

Thanks..

You all should writers of a FileMaker Pro book.

[Ugo DI LUCA]

Hi Macaroni,

The example given for the Adress was an example from many others. Another example could be the price in your product file. What if your price changed and you updated of all records related to that Poduct_ID

When I want to have accurate updated data shown, I would use related fields on the layout. For sure, these aren't key fields.

Your choice is to have it allow modification into the Main file or not, so you can update the Agent record while being in the Invoice File.

You are correct for the rest of your post, even if some techniques (lookup from Modification keys) allow you to update a record as soon as any change is made to that particular record.

To be more concise about my last post, one method I use is "Conditional lookup" when I want to lookup only some fields.

Let say I have a Product File with Product description, Price, Quantity in Stock,...

In some occasions, I'll script a relookup in order to see the updated quanttity in stock for the Product_ID. But Idon't want any change to prices, description,...

Here is the solution :

In the Main file (Product file in that case), create a conditional calc :

Case("Condition" = ?; "Product_ID, Product_ID & " 0")

Use this new calculation as your main Key for the right part of the relationship for those fields you want to keep locked (according to the conditions given).

Use the classic Product_ID for other fields.

Finally, what about the many to many relationship from Proposals to Invoices. Are you sure this will never come up ?