Can't see child records

[gitpicker]

I am using A5v10.5. I have a vendor table (parent) and a vendorcontacts table (child) with vendorname as the primary key in both tables and the set configured as one-to-many. The vendor table currently has 278 records and the vendorcontacts table has 355 records. There are multiple contacts for some of the vendors, thus the one-to-many relationship. The vendor table was created and the data entered prior to creating the vendorcontacts table. Data was imported from an Excel spreadsheet into the vendorcontacts table. My problem now is how to see the appropriate vendorcontacts for each of the vendors in the vendor table. How do I get the two tables to associate on the vendorname key so that for each vendor I can see the matching vendorcontacts? Also, could I create a form with a embedded browse table that would display the proper parent-child relationship as well as allow new vendor contacts to be entered and correctly associated with that vendor?

Thanks,
gitpicker

[Tom Cone Jr]

Re: Can't see child records

Native Alpha Five tables?

If so, you can create a NEW set. Make Vendor table the parent and link one-to-many to the vendorContacts table. Save the set definition. Right click the new set name from the control panel, and choose open default form. This will get you going. The user guide discussion of sets will help you more. If you can't find it let us know.

[gitpicker]

Re: Can't see child records

Thanks Tom. Yes, native A5(dbf)tables. I figured it out. The field values for the vendorname (key index) did not match for the first few records in the the vendor and vendorcontacts tables. I had to scroll down several records before I began to see any records show up in the child table in the embedded browser. Setting up the key index using the vendorname may be a bad idea. Do you think it would be advantageous to change the data structure of each table by adding an auto-increment vendor_id field? And if so, should that field type be character or numeric? If I am going to add the field, I guess now would be the time to do it considering I have not created any forms, browses, queries, or reports yet. What do you think? Also, what is the best way to add the initial vendor_id field values? I know I can use and Update Records operation to Assign Serial Values, and that should work for the vendor table since each vendor name is unique. How could that be done for the vendorcontacts table considering there are duplicate field values for the vendor name? Then if I create the vendor_id field and assign each record a unique vendor_id number, would I then be able to set the field rules for the vendor_id field to auto-increment and, as each new record is entered, it would increment beginning with the current highest value +1?

thanks,
gitpicker

[Tom Cone Jr]

Re: Can't see child records

I prefer to use a vendor_id field, instead of the vendorname, to uniquely identify each record in the parent table. Names can change (weddings, divorces, mergers, breakups, etc.) Also, when you have enough records you're bound to run into two vendors with the same name. Or names that are so close your staff regularly mixes them up. Assign an id field value and then who cares if the name changes down the line.

For native A5 tables I tend to use character data types for my primary key fields. I use numeric fields only if I plan to do arithmetic. So zip codes, phone numbers, and id fields are character based in my systems. It's a matter of personal preference I suppose.

I don't know of an easy, accurate, way to assign foreign key values to the child table. Be grateful you don't have 10x more records.

[Al Buchholz]

Re: Can't see child records

Once you've assigned the id values to the vendor table, create a set where the vendorcontacts links (1-1) to the vendor by name.

Then look at a browse of that set to see if the data of the 2 tables lines up properly.

Use an update operation on that set to assign the id value in the vendor table to the id value in the vendorcontacts table.

Adjust remaining ids manually.

[Tom Cone Jr]

Re: Can't see child records

Good point Al.

Michael, notice that in the set Al describes your child table is the parent, ok?

[DaveM]

Re: Can't see child records

Per Tom:
For native A5 tables I tend to use character data types for my primary key fields.

I absolutely always use numeric fields as in 9,0(no decimals) since so many errors with the character primary fields in the early days and the side effect is that I do not have to worrp about filling with o's.

Tom and I differ on very little really.

[gitpicker]

Re: Can't see child records

Thanks everyone for the help on this. Because of the good advice I was able to get the primary indexes created as vendor_id and, using Al's and Tom's suggestions got them synced up. Everything is working as it should. I deployed my development database and it is now my production database, but I am having a new weird issue with A5 backups. I posted a new thread for some help with that one.

[MoGrace]

Re: Can't see child records

I absolutely always use numeric fields as in 9,0(no decimals) since so many errors with the character primary fields in the early days and the side effect is that I do not have to worrp about filling with o's.

Tom and I differ on very little really.

The only problem with using numerical data for an auto increment is that 10 will be in the sort before 9.

[Al Buchholz]

Re: Can't see child records

The only problem with using numerical data for an auto increment is that 10 will be in the sort before 9.

Only in circumstances where the value is converted to a character and then sorted. Which can happen automagically.

ie a list object or a multiple field sort where the sort field is concatenated with numeric and character data..

A normal sort is fine.

But certainly something to watch and allow for .

[DaveM]

Re: Can't see child records

The only problem with using numerical data for an auto increment is that 10 will be in the sort before 9.

Actually just the opposite unless you pack a character field with O's at the front which is more setup work.
numeric gives
8
9
10
11
and so on.

I moved from character many years ago when the table did not wish to go past around 19000 and something with character. Since fixed(I think). Also much easier to move to sql with numeric primaries.

[MoGrace]

Re: Can't see child records

As Al said - until you want to do something with that ID field that requires converting it to a string. I prefer character fields for ID's because you can add a "C" for customers and a "V" for vendors, etc. Otherwise you have tables filled with Id's that look pretty much the same.

[DaveM]

Re: Can't see child records

I have a different field for that. Clients rarely ever see the connection field. Why would they need to?
But, each to his own. It just seems if it is an incrementing field, adding a c or v would be counter productive?

My biggest alpha app has car dealerships, then departments, then salespeople(mostly)
a car deal(customer) has its own identifying number(incremented and never seen) and it also has another ID field that is what it becomes in the dealership.
Confusing? Not to me. Never show anybody the incremented numbers. They can see the deal number and stock number of a vehicle.

Do I have an extra field here or there? Maybe, but no index issues or search issues like I see others having here from time to time.

Adding my customers are labeled in a field as active, sold, pending, on order or dead deal. My inventory is labeled in a field as active, sold or pending
Vendors are further broken down as to type. Parts, labor, etc

Why would I need to put a character field just so I can add a V or C to it?

Now I have to get back to the website project for a couple hours. I have 3 more google maps and 2 jcalendars to set up.

[Mike Wilson]

Re: Can't see child records

I use character exclusively and pad it with "0", and add suffix of 3-4 characters giving the ID association to what the ID is for. Why?. Because these values go on reports and these reports are saved by these values and having a suffix that delineates what the ID value relates to is more robust. Otherwise if it is just that the customer is 12 and the invoice is 12 and the Project is 12, provides little delineation. And to have it said, during development, this distinction is invaluable! I have done it both ways and I have come to appreciate, and my customers and vendors appreciate the distinctions when a conversation regarding an order, or invoice, or comminication is being had.