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.

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.

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?

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

I can't believe how many times I could have used this script in the past. It might be simple, but with the addition of the final step, Enter Preview Mode, it's a great tip. Thanks.

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.

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.

How about .... eliminating the use of any repeating fields in the future and replacing them with the use of portals as suggested?

After experimenting with this, I realized that splitting the records with existing repeating fields is going to be a lot more work than I want to do right now, and I won't get much benefit from doing so. I think I'd rather spend the time revising the current file by eliminating any repeating fields and converting them to portals, and will start with a new file for future orders. I'm sure I'll be back with more questions, so don't go anywhere.

I have a number of calculations on various layouts so I can look at information several different ways. At first I wasn't sure which table to reference to create certain calculations because of the portal, but it's starting to make sense. Splitting them isn't the problem - it's getting them to show up in the portal. This is the first I've worked with Primary IDs, parent tables and child tables, and I haven't found a reference in the Users Guide to understand the terms better. I'm trying to use the example you posted to understand what I need to do so after the fields are split the data can be seen in the portal. None of the original records had an OrderID which makes it a little more complicated. Hey, at least I got the portal working. Two of them in fact.

I did a search on the forum and found some tips for importing and splitting the repeats. I've done this before, but only to separate records with repeating fields into individual records, and other than some extra steps it looks like a very similar process. If necessary, I don't really have a problem finishing the year with the file using repeating fields and starting one with portals after that. I'm getting a better understanding of how portals are used, thanks to your example, but having to redo the calculation fields is taking me longer than redoing the layouts with portals. Good thing I'm not in a hurry.

Very much so. I've been working on a copy of my current file - no fool am I - and while it's a little slow going, using your example file is a great help to getting it set up. Two things: Is there any problem with me not using an auto enter OrderID, or is that just a user option you built into your file? I'm asking because I have my own system of order numbers, and from a little experimenting it seems like it would accomplish the same thing. Is there an easy way, or any way, to transfer the information in the existing records that used repeating fields to portals? If not, it's not a problem, and assuming I can get everything set up I can start a new file at some point. Thanks again for the help.

It looks like every time that a new line item is added to the portal in the Orders table, it creates a new record in the Line Items table. There could be 4 records in the Orders table, but a completely different number of records in the Line Items table, determined by the number of individual line item entries. It almost looks like the Line Items table is acting as some type of storage area for the data, and maybe that's all it is. The OrderID seems to tie together all the entries in the Line Items table to the related record in the Orders table so that several items share the same Order ID. But the RecordID isn't as clear. Opening your original file without any changes shows a Record ID of 5 and 6 but I don't know what that relates to, since there are only 4 records in the Orders table. Adding a line item to the second record in the Orders table created a new RecordID of 7, and a second line item creates RecordID 8. Why did it start with 5 and not 1?

Thanks. I can see what you've done in the new file, but of course I'd like to know what it's doing, and the purpose of both the OrderID and Line Items Table. If I'm going to make changes, I prefer knowing how something works, in case there's no one around to ask for help in the middle of the night. Also, since I have dozens of calculations based on the layouts with repeating fields, wouldn't they all have to be rewritten if a portal is substituted? The fact is, I've been using the repeating fields for years, and haven't encountered any problems that I'm aware of, but then again, at this point I don't understand all the features of portals and their benefits.

Yes I did. There's a portal with one active row in the Order Entry table, that's related to a Line Items table. I can't enter any info directly in the Orders Table, but even when entering data in the Line Items table, it doesn't appear in the Order Entry table portal. I appreciate the help with the file, but I'm not sure how to use it yet.

I'm not sure what you mean. What data from what repeating fields? I have no doubt that there are other, and better, ways to do many of the things I've been doing. That's why I'm here.

Because wouldn't someone have to enter the approximately 300,000 items for them to appear in a portal? Where else is the portal going to get the information? As I said in an earlier post, I have a very large catalog of items from which to choose, and everything has to be entered manually. Unless I'm mistaken, and it wouldn't be the first time, if I didn't use repeating fields, and I wanted a form with the capability of entering 10 line items, wouldn't I need 10 separate fields for each item, as well as 10 separate fields for quantity, cost, etc.? Maybe not. I'm just asking. Regarding Supplier 1 and Supplier 2, sometimes I have to send an item from one supplier to another supplier to have something done, before it's shipped to a customer. Or, I'll get an order from my customer for two items that come from two different suppliers, but the customer wants everything billed on the same invoice. I deleted several layouts before I posted the file, but the order form actually has two sections, and the capability of sending only certain information to each supplier through the use of different print layouts. That way, when I invoice, everything is already set up so that all the merchandise appears on one invoice.

Sorry. I wound up looking at the post you referenced and downloaded that sample instead of the one in your reply. I had originally tried establishing a relationship using the Orders Table, but that was when I was using = and not x. I see exactly what you've done and made the changes to my file. Works great, and thanks, again. I'm still not clear on the reasons it's suggested to avoid repeating fields. I probably have close to 300,000 items available, if not more, and about 3,000 suppliers from which to choose. Everything does gets entered manually on the order form, and there would be no way to set up a portal as shown in the InvoicesDemo sample. Having said that, are you suggesting that even if everything is entered manually, there's still a better way to do so than using repeating fields? Even though I'm the only one who uses the file I'd still like to try and learn the recommended way of constructing it, and that way I have the option of deciding what works best for me.

Can you explain how the relationship should be set up?

Can you explain what you mean? I don't have a table of items from which to select, if that's the issue, so each item and price gets entered manually with the info taken from various sources. The repeating fields make it easier for me to do the calculations, at least without knowing another way to create them.

I tried setting up different relationships, and the portal seems to work on existing records regardless of which are selected, but not on the creation of a new record. I'm sure it's pilot error on my part. Here's an edited file with the portal, so you can see what I've done. Test_File.fp7.zip

Found one problem. When I create a new record, nothing appears in the portal until I've added a supplier number to the new record, and then the list appears. This defeats the purpose of having the portal, since I need it for reference in the first place. Is there something that I can edit to fix this?

When I first read your reply I had no idea what you meant by a cartesian relationship. I looked it up in the Help file and all I did was change the = to an X in the "define relationships" window which caused the portal to do exactly what I had been trying to get it to do. I had created an edited test file, in case I had to upload something to demonstrate what I was trying to do, and the three suppliers are in numerical order which is perfect. My original file that has about 85 suppliers wasn't sorted but I found the setting for sorting in the portal set up window, and this works great. I can't thank you enough for steering me in the right direction.

I have an Orders Table and a Suppliers Table. Each supplier has a distinct number, so that when I enter the number on the Orders Layout, it uses a Lookup to populate the fields with the supplier's name, address, etc. When I can't remember the supplier's number I have to switch to the Suppliers Table and look up the number, then switch back to the Orders Layout and enter it. I'm not sure that I fully understand the function of a Portal, but can a Portal be added to the Orders Table that allows me to scroll down a list of supplier's numbers and names without switching back and forth? I've been experimenting and tried to add one, but only the first row in the Portal had any information, and it contained the same supplier name and number that's on the current record, not a list that I can scroll to see of all suppliers' names and numbers. If I added a new record, the portal was blank so obviously I didn't have it set up correctly. Can a Portal do what I'm asking, or is that not the way it's used? I've looked in the manual, the help file and also several samples posted in various topics, but I'm not getting it. Thanks.

Okay. You convinced me. I wrote a script following your instructions and it worked the first time, so now I have a few others to rewrite. Always like to learn something new. Thanks.