Multiple files to a related database

[stretch]

Hello all,

I have 3 order creation database files that I would like track via a fourth "Shipment Tracker" file. The three files serve as product order creation but differ in their report layouts. Each of the files that I want to track have a unique serial number in three different ranges (i.e. 100000, 200000, and 300000). Is there a way in that I could relate all 3 serial numbers to a corresponding serial number field in my shipment Tracker file?

[BobWeaver]

A relationship from the orders files to the tracker file should be simple enough. Create a relationship from each order file to the tracker file using the serial number as the key field. But if you need a relationship from the tracker file to the orders files, it gets more complicated. It depends how you want the relationship to work. If you want to have the tracker file figure out which order file in which to look up the related record based on the serial number range, then you could do it with 3 relationships and a calculated field. It's an ugly way to do it.

You should really look at combining your order files into a single file. It's good database design practice to keep the same type of information in a single file. You can add a field to distinquish the different groups of data if necessary. And you can have your different report layouts all in the same file. Then setting up a relationship from the Shipment Tracker becomes a no-brainer.

[stretch]

Hi Bob,

Thanks for the information - I need the relationship from the tracker to the order files. I had thought that a calculation would solve my issue but I was also sure that it would not be pretty.

The reason I have 3 different files is that we ship to three different countries (US, MX, and CA) and each one has a specific set of paperwork requirements. I will try setting up a calculation that would send me to the corresponding reports.

Cheers,

Richard

[BobWeaver]

I strongly recommend that you combine the sales into a single file. It will make future development a lot easier. Like I said before, you can easily generate different reports from the same file -- a lot easier than trying to develop links to multiple files. At some point you may need to be generating things like reports of total sales to all countries. With the data broken into different files it will be a nightmare.

[Ugo DI LUCA]

Bob is correct, there's no need for 3 files there. I'm currently placing orders to 6 different countries and any particular is stoted in the "Vendor" file, so that the Order be printed according to what characteristics have been defined there.

Just switching layout.

[stretch]

I now need to bring all my existing layouts from two of my files into my main layout - is there a quick way to do this or is cut and paste my best option?

[-Queue-]

Use copy/paste and be careful to check each field to ensure that it's correct (or even exists ) after the paste.

[Wendy T]

The other thing that gets lost after a layout copy/paste is the tab order. Re-setting it is especially painful if it has lots of fields and the order is not roughly left-to-right and top-to-bottom. *wry smile from experience*

But it's still easier than re-creating a layout from scratch!

cheers,

Wendy

[-Queue-]

Good point, Wendy! Is there anything else I/we might have missed?