Is a Portal the Answer

[bruceR]

There is still no point in putting it off. In the time you've spent in recent replies, it could have been completed.

Edited November 30, 2010 by Guest

[ArKay]

It's not a question of putting it off. I decided it wasn't necessary to convert the records in the existing file since I'm not having any any problems with it. It's just a matter of making some changes to the way I do things in the future, at the suggestion of several forumites. I'll create a new file with the use of portals, and when it's ready, start filling it with new records.

[ArKay]

I redid my file and have everything working pretty smoothly, except I ran into one small problem.

Occasionally I have to do a two part order so I have two sets of fields on my order form for Supplier 1 and Supplier 2. When I just enter info into the Supplier 1 fields, the Line Items table assigns an Order ID to the entire order, and a Record ID to each line item. But when I also enter info into the Supplier 2 fields, it assigns the same Order ID, which makes sense, but it also references the same Record ID for each line item as it did in Supplier 1. In other words, if there are 4 line items for Supplier 1, the Record IDs would be 1, 2, 3 and 4, but so are the first four items for Supplier 2.

Do I need to set up an additional Line Items table for Supplier 2, or is there some way to set up a additional relationship between the Line Items table and Orders Table so that the RecordIDs for Supplier 2 appear in the same Line Items table as Supplier 1?

I've attached a screen shot of a rough layout which might explain this more clearly.

Edited December 3, 2010 by Guest

[bruceR]

Attach the file (or clone). The image doesn't tell us much. You're also dealing with exactly what we talked about before; not being fully relational. There should be no such thing as Supplier 2. There should be an order_suppliers join table.

[ArKay]

Please let me know if you can tell anything from this or need any additional information. It's heavily edited and I hope I didn't delete too much. Thanks.

Sample_of_2_Suppliers.fp7.zip

[bruceR]

Yes, that is helpful. I'll try to make some mods and upload them but I see that it is still filled with non-relational design.

The whole supplier 2 thing, as I mentioned, needs to go away.

You apparently have a master order and then 1; or 2; or sometimes more? sub orders that go to suppliers. That goal and business reality is better accomplished with a join table as I described.

The summary calcs are also created at the wrong level. Those belong at the order level.

[bruceR]

Along these lines...

Sample_of_2_Suppliers.fp7.zip

Edited December 3, 2010 by Guest

[ArKay]

Please allow me to explain myself..... (inside (file) joke)

Before I make any comments about the design, I want to check that the file is working correctly, unless I don't understand the way it's constructed.

When I add a new record and go to the order entry layout, how, or where, do I add a supplier contact? I can't select the field for ASI, which used to enter the supplier information, and the information added to the order items section on the Line Items table, disappears as soon as I leave the order items section.

Also, since two suppliers per order is the most that I would ever need, would it be possible to use something similar to the original order entry layout I had, where all of the information for both suppliers is visible on the same layout?

[bruceR]

Add records in the supplier orders portal top right by entering supplier ID numbers. Yes.

[ArKay]

When I add the ID numbers, which adds suppliers, and then try to enter items below, the items aren't visible once I leave the Selected Supplier Order Items fields. I can add additional items to the first record you created, but not new ones.

Before this goes any further, there's never a need for more than two suppliers on any order, and even that might only occur in 1 out of 25 orders, in case that would change the reason you suggested doing it this way.

[bruceR]

No reason to change anything. Not true. But you DO need to select the order you're going to work on by clicking it. You will see that it is highlighted once selected. You seem to be approaching your request list as if you're a paying customer. You're getting a lot of free help, at some point you need to do the work of refining it.

Edited December 3, 2010 by Guest

[ArKay]

I'm sure I've already surpassed the list of free requests, and appreciate all of the feedback you've given me. And while I don't think that this version of the layout will work for me, I'm trying to see how much I can decipher and incorporate into a layout more suitable for what I need.

I'm still not 100% clear on what an orderID or recordID is used for, other than referencing specific orders or line items in related tables. In other words, if the record ID is the same for the first line item in what I like to call supplier 1 and supplier 2, how does that affect the results? I'm trying to understand why I can't use the original layout I submitted since I don't really have a need to look at the line items table. All of the information is entered on the orders table, and it's exactly the same.