Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2012
    Posts
    6

    Probably very simple question for you guys

    I am building a database in Filemaker for a new company we are launching in Brazil. I am very new to database development. I understand the theory of many to many relationships and how to solve them using a join table, but i don't understand in practice how to use the join table, meaning what fields to populate with.

    I have two tables, "customers" and "equipment" that have a many to many relationship as all customers will have various equipments installed and all equipments will be installed to various customers.

    So I created a Customers/Equipment jointable.

    "customers" table has
    Customer # (pk)
    and usual fields (name, address, etc)

    "equipment" table has
    Equipment # (pk)
    and fields such as (name, description, price, brand, etc.)

    "customers/equipment" table
    customer # (fk)
    equipment # (fk)


    I want to populate in the customers table which equipment is installed in their house but am not sure how to do it. Any help?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    You don't populate it in the Customers table. That's the whole point of the Equipment and CustomersEquipment tables.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Will all customers pay the same price for the same equipment
    Is there a need to record serial numbers
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Oct 2012
    Posts
    6
    Hmmm, maybe i worded it wrong. I know that I don't want to populate the data into the customers table. I have a customers layout and i want to be able to setup a portal that will allow me to choose the various equipments they have installed and the monthly rental cost in order to total up their monthly bill cost.

    Sorry if I seem a bit slow, I am really new at this.

  5. #5
    Join Date
    Oct 2012
    Posts
    6
    All customers will pay the same price for the equipment, i do not need to record serial numbers, I actually don't even need to track the equipment, it is more just for the total monthly bill cost to calculate the comissions we will receive from the sale. The commission is based off the month bill price.

    The other thing that is hanging me up a bit is that the retal prices of the equipment will change over time and if I go into the equipment table to update the price it will change the rental contract price of the older records also.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    The CustomerEquipment table records what customer has what equipment. At the very least, this table needs to store the PK (Primary Key) of the Customer and of the Equipment. I would strongly recommend that you include other information about the relationship such as when the equipment was acquired (so you know when to start charging for it) and when the equipment was retired/returned/etc (so you know when to stop charging for it). Things always seem simple at a given point in time, but databases track information and the business usually wants to keep that informnation "forever", so knowing when it is applicable is usually an important tidbit of information to keep around!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Oct 2012
    Posts
    6
    Man i don't know why i am unable to get this part. I have been able to program everything in file maker and the tables and data are ready but i can't seem to figure out how this join table works. I added a screen shot of the relationship diagram from Filemaker. If someone can help me understand what i am not seeing it would really help me out.

    I am working in a clients layout where i am saving the pdf copies of the contracts, voice recorded welcome calls, client contact info, and I would like to list the equipment they have and the monthly price for that equipment.

    I understand how to link to my employee table as it is a one to many relationship with the client table being the child. I open a field with the foreign key of the employee table and then base the pop up menu off of the primary key of the employee table and then showing names. Works great.

    I want to do the same thing with the equipment list in the client layout so when the client layout is open you can see the equipment list they have and rental costs and then do a sum to get to the month bill price. I open a field to the FK of the joining table between clients and equipement and then direct the popup list to the PK of the equipment table, but nothing happens.

    I know it because i am not understanding and missing a concept of the join table and it's function. I have spent the last couple of hours trying to figure it out to no avail.

    Any help?

    the diagram names are in portuguese because i am doing this in brazil
    Attached Thumbnails Attached Thumbnails diagram.jpg  

  8. #8
    Join Date
    Oct 2012
    Posts
    6
    Well I figured out the problem i was having, i didn't have the box checked in the relationship detail that allowed me to create records in the join table from the client table in the portal. This is the reason nothing was showing up my portal.

  9. #9
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Good to hear you have it working - thanks for posting the problem/solution

  10. #10
    Join Date
    Oct 2012
    Posts
    6
    one other thing i figured out was how to solve the problem of having the rental values change over time without affecting the old contracts. Use "look up values".

    Just in case there is another total newbie reading this

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •