When Good DB Design Goes Bad

[Sly_Squash]

Hello all.

I have a file, Customer.fp5. It contains a field CustomerID, which is a Number, Indexed, Auto-Enter Serial, No Modification.

I have a file, Computer.fp5. It contains a field CustomerID, which is a Number, Indexed. By copying the CustomerID of a record in Customer.fp5, you effectively Assign a Computer to a Customer. It is a 1:m between Customer and Computer.

I have a file Printer.fp5. These Printers are networked, so there is a m:n between Computers and Printers.

Since there is a m:n between Computers and Printers, I created file called ComputerPrinter.fp5 to hold which Computers have which Printers assigned to them. This file has a field ComputerID, which is a Number and is Indexed. By creating a record in this file that holds a ComputerID and a PrinterID, you effectively assign a Printer to a Computer.

I have a file, PrinterTypes.fp5. The relationship from PrinterTypes to Printers is 1:m. One Printer Type (i.e. HP Laserjet 5L) can belong to several Printers, but each Printer has only one PrinterType. For this file the primary key is a field called Model, which is Text that is Indexed, Strict, Required, and Unique. By copying this Model field into a Printer, you effectively assign a PrinterType to a Printer.

I have a file, PrinterEquipment.fp5. This consists of toner, drum kits, etc. that can be used with a given PrinterType. Since one PrinterEquipment may be used with multiple PrinterTypes and one PrinterType uses several PrinterEquipments, there is a m:n between the two files.

Thus, I have a file, PrTypePrEquip.fp5. By creating records in this file that have a Model from PrinterType and a Type from PrinterEquipment you can effectively assign PrinterEquipment to PrinterTypes.

I wanna show all Printers assigned to a Customer through their various Computers in a portal. I would first have Users select a Customer from a portal that uses a Constant Relationship to Customer and copy the CustomerID into a global gCustomerID field. I do not deeply care if duplicate Printers appear in the field. I would like to do this in a layout in the prTypeprEquip.fp5 file. The reason is because I would like to provide a feature so that, when a Customer calls in and says "I need toner for my Printer" we can look up that Customer, find click which Printer we think he's talking about, and instantly display all of the PrinterEquipment that goes with the PrinterType that goes along with that Printer.

Any questions?

Hey, you made it to the end. Congrats! Thanks in advance.

~Sly_Squash

[Fenton]

The basic technique here uses the fact that a multi-line global field (text) can be related to a field in a file, producing a match of all records that match any line in the global.

Beginning with CustomerID it's a simple one to many to the Computers.

From then on though the relationships are many-to-many. So you need to capture the many Computer IDs into a return-delimited list.

You can use ValueListItems["file name", "value list name"], with "filtered" relational value lists. It's hard to explain in words, and it has a 32,000 character limit, but here we go.

Create a value list in Customers, "filtered" by a relationship to Computers, based on CustomerID, named "CustComputers."

Create a calculation field, Unstored, text result, "_cCustComputersVL" =

ValueListItems (Status(CurrentFileName), "CustComputers")

This field will be a return-delimited list of Computer IDs for that customer.

Create a relationship from this field to the ComputerPrinters file, _cCustComputersVL =:ComputerID.

Base your portal in Customers on this. Should show the printers for that customer.

Create a global field _gPrinterID.

Create a relationship from this field to the PrinterID in PrinterType, "PrinterType_PrinterID".

You would set this global field by clicking a line in the portal. You can show the current printer selection in a related field above the portal, for reference.

Create a calculation field, Unstored, text result, "_cgPrinterType" = PrinterType_PrinterID::Model

Create a relationship from this field to the Model in PrTypePrEquip.

Build a 2nd portal on this relationship.

I think this will show the equipment available. I may have simplified the structure too much. I can't be sure unless I built all the files, which I'm too lazy to do.

[Ugo DI LUCA]

I would first have Users select a Customer from a portal that uses a Constant Relationship to Customer and copy the CustomerID into a global gCustomerID field. I do not deeply care if duplicate Printers appear in the field. I would like to do this in a layout in the prTypeprEquip.fp5 file. The reason is because I would like to provide a feature so that, when a Customer calls in and says "I need toner for my Printer" we can look up that Customer, find click which Printer we think he's talking about, and instantly display all of the PrinterEquipment that goes with the PrinterType that goes along with that Printer.

Seems like you have your answer here.

Now, I would do this in the Customer.fp5 with a Portal to the Computers.fp5.

Then, Id use other GTRR or global fields to the Printer Equipment File.

[Ugo DI LUCA]

Hah,

I missed the one to many from Customers to Computers...

Now, that's one reason why I quite always build a Many to Many with a Join File instead of the classic One to Many.

Who knows how your business would evolve.

[Sly_Squash]

Thank you for the assistance. I will make an attempt to implement Fenton's idea and post the results.