Probably simple to you but is driving me nuts

[somasf415]

I am very new to databases and am trying to develop a system for a company we are launching in Brazil. I am having trouble with understanding how to use a jointable in filepro 12.

The main gist of the the database is to do the following. We are a 3rd party rep to sell services for a partner and we receive commissions on those sales. We have a sales team making sales and the partner company pays our company for each sale and then we pay our reps a portion of that commission. The clients sign a contract and receive equipment that has a monthly fee along with services that also have a monthly fee. The database is to store pdf copies of all the contracts, track sales for employees and help calculate their commissions which is based off of the monthly rental amount for each contract.

I set up a table with all of the reps data and a table with all of the available equipment clients can rent and services both with their fees.

I have 3 main tables, "reps", "clients", "equipment",

The problem lies in the "Client" table. The "reps" to "clients" is a one to many but the "clients" to "equipment" is a many to many so I created a join table "clients/equipment"

I am working on the "client" layout where i want the following abilities. To input the client information (name, address, etc.) , assign each client to a sales rep (i have already succeeded in this), save pdf's and a sound file of a welcome call (i have already succeeded in this also), and also track the equipment and services each client is using in order to calculate what their monthly cost is ( have failed miserably in this)

I understand the basic theory of why you need a join table but i think i am missing the practical part. I don't know how to populate the "client/equipment" join table

For example in the client list i assign each client to a sales rep. I so this by putting in a field with the foreign key of the "reps" table and then using a pop out menu with the values being linked to the primary key of the "reps" table and linking that to the names. That is working fine.

I tried to do the same for the equipment list but this is where i get lost. I created a portal and tried to populate it with the foreign key of the "client/equipment" table and then link to the primary key of the "equipment" table similar to how i did for the "client" to "rep". But it comes up blank.

Any help would really be appreciated. I have spent many many hours on this problem and am not getting it. I know i am just missing some basic concept that is not clicking for me.

I included a screen shot of the relationship diagram and the client layout i am working on. The fields are in portuguese.

[Ocean West]

In leiu of using the portal to create the records most developers use a complete scripted method to create the JOIN record and the alternate entity record

In short if you are on the client - and you press a scripted button.

the script would capture ID's of the current record (globals or global variables)

spawn a new window or switch layouts.

go to list layout - some utility layout based on REP

OR

a Utility layout that is a portal for all REPS that with a structure that allow users to search or filter the list.

you would allow the user to click another button to make a selection

(or branch and allow them to add a new rep)

capturing the selected IDs to variables

once selected your script switches layout the join table

creates a new record setting the fields to the captured variables

then dismiss the window or returns to the original window.

-

You would build a similar script and layouts for the inverse when on the context of the equipment

showing the list of clients

Hope this helps.