DUPLICATING RECORDS IN A SEPERATE DATABASE

[howard shidlowsky]

Hi Alpha 4 users, I need some assistance. ...can you help me? I am using Alpha 4V6. DOS, Windows 98. I have four separate identical databases. Each of these databases are used for storing records for separate corporations. Each of the four databases have identical database structures, fields, field lengths, and indexes. The only thing different in each of the databases is the actual data. Although each record is unique in each database, about 20% of these records contain identical data in a field known as "FOLIO_NO", which is each record's identification number. In addition, this idential data in the FOLIO_NO field may contain identical data in some or all four. databases. On a daily basis, we add new records as needed, into each individual database. As necessary, we also updated data into our existing records.

MY GOAL: I would like to create either a fifth database, or a SET, that will combine the records from all four databases together so that I can view all combined records. In addition, each time we add or update a record in ANY of our four databases, we would like that information to automatically become part of the fifth database or set, for later review and editing.

QUESTION: How can I set up a fifth database or SET that will contain all of the combined records of all my four existing databases, so that evwrytime I creat a new record, or edit an existing record in anyh of my existing four databases, I can automatically "post" or "add" that same information to a fifth database to be known as "NOTES" Database or Set, using the FOLIO_NO field as the "link." I believe that if I make a Set and even if the "Folio Number" is the linking field, they only records that will be displayed are those that share identical FOLIO_NO data. This will not serve my purpose.

How do I link all four databases together so that I can combine, view and edit ALL records by FOLIO_NO and still be able to add new records at any time.

I would appreciate any help you can give this beginner.

[Tom Patten]

RE: DUPLICATING RECORDS IN A SEPERATE DATABASE

I'm not sure I understand your queston fully but you could:

1. Create a new database called Header (or any other name) to hold the field FOLIO_NO which is unique to each company. I would also suggest adding company name. Enter the four unique identifiers into the Folio_no field.

2. Create a duplicate of one of your current databased (which are all the same). Then append all the data from the other three. So all the company are combined with a unique identifier of FOLIO_NO.

2. Create a SET structure using Header for your parent database and add the append database from step 2 above as your child (one to many) Set referential integrity to Folio_No.

Now when you select the folio number in the header, all the records show in the child.

Hope this helps.

[Robert Picard]

RE: DUPLICATING RECORDS IN A SEPERATE DATABASE

Hi Howard

If you have a set and add a new record in the parent a matching record will be created in the child.

So if you want to maintain separate tables but want to view data for all the tables when needed, I would suggest the following.

Duplicate one of your Tables (Unified.dbf). Append all of the records from the four tables to Unified.Dbf. Next create 4 sets using Unified.dbf as the child in each set.

Now you would have to work out which field to index on and the links, but this might help you get started. Thomas may understands you question better than me, be aware that in a one to many set depending on the number of records in the child table performance can take a big hit. Escpecially if you have a viewable region for the Child Table (Unified.DBf)and you could hit the 10,000 record limit in your region.

Hope our suggestions help.

Robert

[howard shidlowsky]

RE: DUPLICATING RECORDS IN A SEPERATE DATABASE

Hi Tom:

Many thanks for your suggestions.

What I am really trying to accomplish is to automatically duplicate the data in the FOLIO_NO field every time I add a new record. this way I can create a seperate database with on the FOLIO_NO data. Then I can use this FOLIO_no as my Parent in a Set that I will create. Thereafter, any time there is a matching folio number in any of my four databases, it will automatically be displayed in the Set. However, it appears that I cannot "automatically" post the data in my FOLIO_NO field to another fifth database ?

..... any suggestions ??

thanks for your response

Howie

[howard shidlowsky]

RE: DUPLICATING RECORDS IN A SEPERATE DATABASE

Hi Robert

Many thanks for your response.

I just sent Tom a reply to my problem, and perhaps you can offer some suggestions.

I am trying to "automatically" post just my FOLIO_NO field data to a new fifth database that will only contain the data from my FOLIO_NO field. Thereafter, I will create a Set using the FOLIO_NO field as my Parent.

..... any assistance will be GREATLY appreciated.

Thank you,

Howie

[Robert Picard]

RE: DUPLICATING RECORDS IN A SEPERATE DATABASE

Hi Howard

Is "FOLIO_NO" field the only field in your table?

Two ways I know to link 4 Tables in a set so you can view all the records is to have a Parent table with the 4 tables linked "one-to-One" as a child of the other. i.e.

Parent
|->Table1
|->Table2
|->Table3
|->Table4

Have you thought of creating a Single table with a single field and single record. This table would be your master and the value would be matched by matching field for every record in all 4 tables.

Parent table->"All" field->value = 1

4 tables with "All" field. Every record would have a value of 1 for the all field.

You would then create a set "One-TO-Many" using the "ALL" field in the parent and child database.

Parent "All" field to Child "All" field
|=>Table1
|=>Table2
|=>Table3
|=>Table4

Hope this gives you some ideas. My suggestions aren't to different from Thomas.

[howard shidlowsky]

RE: DUPLICATING RECORDS IN A SEPERATE DATABASE

Hi Robert

Thank you for your input.

I will try your suggestions.

Thnk you,

Howie