Setting up complex relationships

[brwnis]

After several attempts I have gotten very close, but have been unable to get all my relationships to work correctly. Here is what I am attempting to do:

Contract Database:

-Each Contract has a unique ID code already assigned

-Each Contract has three sections: Purchaser (Contact), Products, Invoice Schedule

-Each Contract is related to 1 Company and 1 Contact

-Each Company may have more than one Contact

-Each Contract may have many line items of Products

-Each Product line item includes date fields, number fields and text fields

-Each Product is associated with a Supplier

-Each Supplier is associated with more than one Product

-Each Product line item must calculate a field based on a manually-entered Price (which also is included on the line item with the Product info)

-Each Product line date corresponds to and Invoice Date at the bottom of the Contract

-Each manually-entered Price is used to calculate an Invoice Price

*I would like to be able to have a data-entry layout where I can enter the Contract ID code, the Contact info,enter the IDs for the Products, the Prices for the Products - THEN have FM (a)determine if this is a new or existing Contact(: create a new Contact if it is in fact new, © pull up all the related info for each Product (d) Calculate the amounts in each Product line based on the Price (e) insert an Invoice Date based on the Product date and (f) calculate the Invoice Amount based on the Price.

I have a Data Entry table related to Company Table related to a separate Contacts Table. The Data Entry Table is also related to a Products Table which is related to a Suppliers Table.

However, if I have many Products on one Contract, all of which need to pull info from the Suppliers Table and do separate calcs, is this what a join table would be used for? I've read a lot about joins and portals, and was thinking that including a portal on my actual Contract Layout (when I get there) that showed joined records from a Join table would be the way to do that - does that makes sense?

If so - some advice on Join tables (or examples) would be really helpful - everything I've seen so far has had a join table that shows just the corresponding IDs and maybe one extra field - am I missing something?

I was hoping to lay-out the Product line items in the Join tables, so I could just have the portal point there, but that would put a lot of fields in the join table, so I have a sense I must be missing something conceptually.

I actually really enjoy this stuff and am having a great time learning, but I've spent so much time building and re-building I'm afraid my head is spinning a bit... can anyone help me? I really appreciate it!

Edited October 13, 2005 by Guest

[T-Square]

Well, I don't have time right now to go through all the complexities of your setup, but how about this for beginners (basic fields listed, other fields added as necessary):

Tables/fields:

1) Contracts

ContractID, PurchaserID, ContactID, ...

2) Products

ProductID, SupplierID, Price, ...

3) Companies

CompanyID, ...

4) Contacts

ContactID, FName, LName, Phone, CompanyID, ...

5) Suppliers

SupplierID, SupplierName, ...

6) LineItems (this is your join table)

ContractID, ProductID, Price, ...

Joins:

A) Contracts::ContractID -> LineItems::ContractID (this lets you see all the products on the contract).

Contracts::PurchaserID -> Companies::CompanyID (This tells you which company is the purchaser)

C) Contracts::ContactID -> Contacts::ContactID (This relationship is made so that you know which name is active on a given contract; without this, you'd have to list all people for a company)

D) Companies::CompanyID -> Contacts::CompanyID (this lets you figure out both what company a person works for, and which people work for a particular company. You can use this relationship to limit the people on a contract to only those people linked to the active company)

E) Products::SupplierID -> Suppliers::SupplierID (This will let you know who supplies a particular product)

F) LineItems::ProductID -> Products::ProductID (This lets you bring in all information about a product into the listing of products on a contract. You can also use this relationship to set the ProdsOnContract::Price value automatically based on the Products::Price value, which you can later edit if you want.

Invoice totals will be a calculated field in the contracts table that will sum all the prices entered in the LineItems table, based on relationship A above. Basing on this relationship, Filemaker will only "see" the LineItems that are linked to the current contract, and your total will reflect this.

The join table exists primarily to allow you to create an unlimited number of lines on the invoice. When you need a new line on the invoice, you add a new record to the line items table, select the product in question, and it's done. The reason these tables are sparse is that you can derive all the information from the linked tables without adding it again. In other words, each LineItems record can pull out all the information from both linked tables, making it unnecessary, for example, to enter the product name each time. You will want the price entered in each lineitem because you want to be able to change it for a given customer (say), and you also want a historic entry of how much they owed you.

With regards to the data entry thing, I would not recommend having users enter contact information and THEN checking to see whether that contact is in the system. There's too much room for error (was that Charlie Brown, or Chuck Brown?). Instead, I would have a drop down list of people (linked to the selected company), with a button that allows the user to add a name if it's not on the list. That will cut down on data entry errors.

I hope this gets you moving along...

David

Edited October 14, 2005 by Guest

[brwnis]

T-Square - you rock!! I knew my original post had laid it on thick and had just about given up on getting any kind of answer..! I really really appreciate your help. I'm going to try this tonight and see what I can put together. I'll let you know if I come up with questions (and will do my best to keep them brief!) Thanks again!!!

[brwnis]

T-Square,

I now have the tables and joins set up as you suggest. I keep stumbling over what I am sure is a very basic point about how to set up a Join table, and I am sure I'm probably just over-thinking, BUT:

I have one record per contract, and one record per product, but each contract may have many products. So, in my LineItems (Join) table, I should be able to have a field for the Contract ID, and then do I need to include lots of Product ID fields? Seems like there should be a better way than to just duplicate the field 20 or 30 times, but if I only include one Product ID field, won't I only be limited to one Product per Contract?

This might be the same question put another way: I'm assuming my LineItems table will house all the information I eventually want on my contract about the products as well, and the per-product calculations (I need to calculate the Total Fee, included on the Products table divided by the number of Supplier Locations from the Supplier Table, which is related to the Products table) that I need will be done in this table. So, in LineItems do I need to include say 20 Product ID fields, 20 Price fields, 20 Supplier Locations fields, 20 calculation fields, etc?

Again, I realize this is probably very basic for most - I truly am grateful for the help.

[T-Square]

The LineItems table *can* be as simple as this:

ContractID,

ProductID,

Price

Each row in this table, then, represents a single line in your list of products on a contract. You don't create duplicate ProductID fields; you create new rows in this table for each LineItem.

Your interface needs to be built to handle the display of these separate rows--that's what Filemaker's portals are for. On your main Contract layout, you create a portal that is based on the *linked* occurrence of the LineItems table. Think of it this way: on your Relationships graph, you have an *occurrence* of the LineItems table. For sake of clarity, I will call this occurrence "ItemsOnContract". This occurrence (you will see people referring to "TO"s -- that's Table Occurrence to you and me) has the relationship described in A above. BTW, you will want to set that relationship up to allow you to create and delete items in ItemsOnContract.

Now, put ItemsOnContract::ProductID on your portal, and make it into a dropdown list that is based on the entries in Products (create a value list based on the Products table that includes the ProductID and the Name and use it for the dropdown). Finally, put ItemsOnContract::Price in the portal, and set its default to LineProdInfo::Price (LineProdInfo is the TO of the Products table that is linked in the relationships graph using relationship F above). You may have to go into the Define fields for the table to set this up. (While you're at it, make sure that the Overwrite checkbox is set correctly so that it does NOT overwrite existing entries [i don't have this particular in my head; it's right next to the default value entry area]).

Now, when you're in this window for real, you should be able to simply select a product from the dropdown, and it should automatically put the price into the price field, which you should be able to edit to your heart's delight. For additional usability, you can add the LineProdInfo::ProdName field to the portal, and it will magically tell you the name of the products. You will probably also want to create a button on the portal that allows you to delete the line item record. This should be scripted (rather than simply creating a "Delete Portal Row" button, as that can yield errors).

I hope this moves you forward.

Edited October 18, 2005 by Guest
minor typo

[brwnis]

T-Square,

You have no idea how much your help is appreciated! However, I'm afraid I got lost in the middle of your instruction.

I created the portal using the linked LineItems table as you instructed, but I need more than just a Price linked to each item. I had constructed the LineItems table as I thought you would given that I need all those items, but I'm afraid I must have done it wrong. I have posted the db and perhaps you could take a look and tell me where I am going wrong? You'll see that I've included two portals in Contracts, actually, as I need to show Product information from LineItems in one portion of the Contract, and then Invoice Dates and Amounts from the same LineItems table in another portion of the invoice. Hopefully it will make sense.

Again, I can't tell you how much I appreciate your help with this - I am the only person I know using this product and I love it, but have no one else to ask! Thanks again!

ContractsDemo.zip

[T-Square]

I will take on the first part here. See if you can't take it the rest of the way...

Take a look at what I've done. I changed the field structure some, setting up your ID fields as numbers. They really only have to be unique numbers--if you try to put in special readable meaning, my experience is that it only makes for more work down the line. However, you should read up on Filemaker's "issues" with regard to "Unique" numbers before you go too far.

So, they're number fields that are autoentered with the Filemaker recordID. This number is then used for the relationships. While you're there, you can see the cDelete calc field that I use to automate the portal row delete.

Tabbing to the relationships, you'll see that I changed the name of the LineItems TO for my own clarity (I find that using the default table name on the relationships graph is uninformative and confusing, so I try purposefully to name the TO something more descriptive). I also altered the relationship between tContracts and ItemsOnContract to delete LineItems when the contract gets deleted. This ensures that you don't have any lurking unlinked LineItems (say that five times fast).

Notice on the layouts that I changed your linked ID fields (ContactID & productID of Contracts, companyID on People) to dropdown menus, using value lists based on the fields in the linked tables. And I put my cDelete field on the portal row, and assigned it as a button to a quick DeleteThis script. The script is necessary because in the portal, there's always an extra empty row at the bottom, and if you try to delete that empty portal row, FM throws an error up. That's also why I use the calc field and have text if there is a ContractID, and nothing if there isn't.

Have fun!

David

ContractsDemo.zip

[brwnis]

Lurking unlinked lineitems lurking unlinked lineitems lurking unlinked lineitems... three is about all I can manage!

David, all kidding aside, thank you so much. This has gotten me over the hump, so to speak, and cleared up many things I was not quite sure of.

I've got many parts of this now working the way I want them, and have mostly formatting left to do! One more question before I get out of your hair, and please let me know if the correct protocol would be to move this to another forum or just wrap this up here with you:

I can't seem to get the Contacts drop-down menus to be based on the Purchaser ID. Ideally I'd like to insert the Purchaser ID and have only those Contracts associated with that particular Purchaser show up on the drop-down. How do I manage something like that?

Again, I can't tell you how much help this has been to me. I really appreciate it - you have opened up this product for me.

Thank you!!

[T-Square]

Your last question still fits here. What you'll want to do is add a TO (let's call it ContactsForActivePurchaser here, or CFAP) based on the Contacts table that relates the Contracts table to the Contacts table *using the PurchaserID*. Then, build your value list on the CFAP TO (That sounds like a dirty phrase in some language). That should give you only the contacts for the active company.

Cheers,

David

[brwnis]

I already have a table called Companies which relates Contracts to a TO of Contacts called ContactsOnCompany linking the PurchaserID in Contracts to the CompanyID in Contacts. Do you mean that I need to create another set of relationships like this one? Thanks again!

Rachel

[brwnis]

OK, so scratch my last question - I was able to just select "use only related records" in the Define Value Lists, and it seemed to be working, BUT, it only seems to acknowledge already-created contacts. When I select a PurchaserID in the Contracts table layout, I now get all the ContactIDs only related to that PurchaserID, which is what I want, but if I go and add another Contact to that Purchaser on the Contact table, it doesn't get added to the Value List unless I go back into Define Value Lists, select "use all records", then go back to Contracts, and select the new ContactID. Then, if I go back and re-select the "use only related records" in Define Value Lists, it will acknowledge the new addition on the Contracts table, but only if I go through those steps. I feel as though I'm missing something fairly basic, but I can't for the life of me figure out what I'm doing wrong. Does anyone have an idea? Thanks again for all the help - this truly is a wonderful resource!!

Edited October 26, 2005 by Guest

[T-Square]

Rachel--

I can't help you with the last issue, but I seem to recall reading some threads about flushing joined cache results.

Try posting this last question in the Value Lists forum.

Cheers,

David

[brwnis]

T-Square,

Thanks again for all your help - you have been a lifesaver in this process! I will take your advice and move on (for now!) to the Value Lists forum... thanks again!!

~r