Entering data into records in related tables

[drumorgan]

I have three tables, Customers, who can have multiple Homes, that can have multiple Jobs.

For a new customer, I would enter all their Customer info, their Home info, and the info for the specific Job we are doing. But, after that, if they call me back, I want to enter a new job, either for the same Home or possibly for a new Home for the same Customer. Should I be using a layout for Customer? or for Job? and when I enter the new Job info, how can I make sure to relate that record to the right Customer and Home? If I type it in, it will make a new duplicate record for those first two tables. Is there a way to search and link to the existing records?

Again, I would like to do two things. Either add a new record to Jobs with a new home for an existing Customer. And, I would like to enter a new record in Jobs for an existing Home already related to an existing Customer.

[bcooney]

Hmm..I'm sure that there are several ways to approach this interface.

Each Job needs a HomeID. Knowing the CustID can help you narrow the Home choices.

Some ideas:

1. New Job button on portal of Homes on Customer form. This'll capture the HomeID for you to populate in the Jobs record (I'd also add a CustID to Jobs, to that you'll easily be able to report Jobs by Customer; it could be a lookup field). You could also have View Jobs (gtrr Jobs) on this portal.

2. New Job on Job form starts script that takes user to Customer Find/Select. By populating the Jobs::_kF_CustID, you could then provide a conditional value list of HomeIDs. How many Homes does one Customer typically have? If not more than 10, then a popup menu on a Job form will be adequate.

hth,

Barbara

[drumorgan]

Thanks. I'll start playing with the buttons and their abilities. So far, I have the homes in a portal on the Customer Table. I have the relationship set so that I can create a Home from the Customer Table. So, brand new Customer (creates a serialized unique Customer_ID) and enter all data for that table and in the portal enter the Home data, and it creates a new Home record (complete with unique Home_ID and even the correct Customer_ID) linking it back to the Customer.

Yes, customers mostly have one home, but I have a few with two or three. Ten would be NOT very common.

I guess a button for "New Job" on the Customer layout would be the next step. And, the job should have the Home_ID and the Customer_ID? OK, I'll try that. I'm getting into new territory for me here, but I'll keep you posted.

[bcooney]

Remember, you need to grab the HomeID, so put the New Job button on the Home portal row.

pseudo-script of new home button on portal row:

set variable $homeID ; cust_Home::_kP_HomeID

Go to Layout (Job form)

New Record

Set Field (Job::_kF_HomeID) ; $homeID

As I said in my previous post, have the job::_kF_custID be a lookup from Homes of the home::_kF_CustID when the _kF_HomeID is populated.

[drumorgan]

To continue this process... Thanks for the help so far. I have not needed to use a button. I just add the "home" and "job" data right on the "customer" layout. With one home and one job, they all line up as it automatically enters the matching pk and fk.

To add the subsequent homes/jobs I am using a portal. So far so good. AND, even better, I made the fk field a drop-down list filled with the values from the Customer's actual existing primary keys. So, as I add a new job, I can pull down and select between the home choices for that particular customer. It took a few clicks to get that to work, but it actually does.

NOW, the issue is one field is acting up.

Only the top record in the portal is filled and IF I click in the second, it becomes active and IF I add data to any of the fields, it creates the second record. (and even lets me delete them individually)

I am choosing between "primary key" of the "home" table, and inserting it into the "foreign key" of the "job" record. But, I don't have people's unique IDs memorized, so I added a field to display the active "AddressStreet" field and I'll know which house I picked.

BUT, for some reason, when I add that field, it displays a duplicate in the next empty record as if I have entered it. If I have two jobs, it will display in the third record. Three jobs, it shows up in the fourth empty record row.

The only difference I can think of is that the fields that behave properly (i.e. stay only in their row) are from the "job" table, while the offending field is from the "home" table. Just testing this theory, I change the field and get the same mis-behavior for any field from the "home" table.

[drumorgan]

Working through this, I eliminated the second field showing the address, and simply augmented the value list to show the second field as I select. So, it shows the ID and the AddressStreet, while only entering the ID into the fk field.

Tricky stuff, but fun to work through it.

[Ron Cates]

Just glancing at this thread and I noticed from the image of your relationship graph that your customers primary key does not appear to be set as unique as evident by the crows feet on the parent side of the relationship. You can see that the relationship between Homes and Jobs is correct as there are crows feet on the child side of the relationship only. Just thought I would point that out.

Cheers :)

[drumorgan]

Thanks for the catch. I guess I missed that. I am learning this from scratch, and while I have a general idea, I have not played with this type of app in a while. I am almost through with the Lynda lessons and just purchased The Missing Manual. I'm sure I'll be past these freshman questions soon.

I must say, that I am so thrilled to be beyond Bento and am excited to see all the extra things I am now capable of.