Help with customer sales file

[FMMoron]

I currently have a 5.5 file that tracks customers and what they bought. It has worked for a while but my business has outgrown the functionality I built into it.

I recently picked up a copy of version 7 and would like to build it the right way this time. Here's what I have currently.

In the file I have 3 Tables. One for the customers, One for the orders, and one for the detail of each order. My reasoning for doing this is because of repeat business. I'd like to be able to easily see all of that persons orders.

Ultimately, I'd like to display all of this information on one layout. I set it up to display 2 different portals. The main layout is the customer info, and the 2 portals are the order info, and the order detail.

My first problem is that I can't seem to get the order detail to display unless I go to a different layout and put the data in. Then it shows up on my layout.

The second problem is that I would like the portal which shows the "order detail" to only display the information from the record I select in the "orders" portal.

Have I completely confused you yet?? This is just a taste of how I feel.

I've attached an example of the file. I'm just taking a shot that someone is nice enough to take a minute to look at this and give me some guidance.

Thanks for any and all help.

Bob

CustomerFile.zip

[RalphL]

I have worked with your file. I have made a number of changes. I added an auto-entered serial number for CustomerID. Using e-mail address is a poor choice for a key field. These change often. I addded a table which I called Control and a table which I called Global. I adjusted the position of the data in the portal Order Detail. They were too high and other rows would not display.

In the portal Orders I added a checkbox which is from the table Control. This required another value list, Checkbox. This is also a button that runs a script "Select Order".

In the view you will see how I related the table Control to Order and Order Detail. I used a Cartesian Cross Product to TO Control. Via this relationship the Order Number is set. Order Number is then used a the key field in TO Control 2 which is used to filter what records show in the Portal. I found this method in a sample file from New Centry Data.

I have another TOG on the graph with the TO's Orders 2 and Order Detail 2. This provides the relationship to auto delete records in Order Detail.

[Fitch]

I think you'll want to script the creation of orders, so when you click a button it will create a new order record and an associated order detail record. It would also put the currently selected order number into a field, let's call it OrderSelected, and the detail portal would be based on this field, rather than just the Order Number. This will enable you to click on a button in a given row in the orders portal; a script associated with the button will set the OrderSelected field; the corresponding detail records will show. You'll also probably want to make a calculated container field in the Orders table, something like Case(Order Number = OrderSelected; globalGraphic). This way you can lay the calc container behind the other fields on the Orders portal and it will highlight the selected row.

You may want to script the creation of new Order Detail items as well. If you want to be able to just add them by typing in the portal, you'll need to change the relationship to allow record creation.

Don't forget in FileMaker 7 when scripting record creation to add the Commit script step.

[FMMoron]

Ralph,

I can't thank you enough for the work you have done on this. I can safely say there is absolutely no way I would have come up with that, AND it's exactly what I was looking for. PERFECT!

Thank you very very much.

Bob