Is a Portal the Answer

[ArKay]

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.

[bruceR]

First of all, a portal has to be based on a relationship and the nature of the relationship definition has to be based on the intended function.

In your case, you probably want a cartesian relationship (select the "x" as the symbol defining the connection from Orders to Suppliers. When using the "x" relation it does not matter which fields you use as the left-right match fields. For all other relations it DOES matter.

Secondly, you need to place fields in the portal that come from the correct table occurrence.

Regarding the "x" relation method, this shows you all records in the table. If your list of suppliers is modest, then the x relation works fine. However, if you had many hundreds or more, you would not want to scroll through that long list and so you'd need some kind of filtering method.

[ArKay]

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.

Edited November 28, 2010 by Guest
Sorting problem solved

[ArKay]

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?

[Vaughan]

Is the supplier number used in the X relationship for the portal? I bet it is.

Instead, create an auto-entered serial number field in the table. Use this for the portal relationship. This will get a value every time a new record is created.

[bruceR]

In an X relation it doesn't matter. You can even delete the match field.

It sounds like the relation may have been improperly set up and is using two match fields.

[ArKay]

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

[Vaughan]

Bruce, the X relationship was using 2 match fields. Nice catch.

Ron, you should be using unique auto-entered serial numbers as primary keys in for the relationships. The mixture of related tables and repeating fields suggests that the data design needs a serious make-over.

[comment]

See if the attached helps.

Note:

You should be using a related table for order items, instead of repeating field. See an example here:

http://fmforums.com/forum/showpost.php?post/309136/

Test2.zip

[comment]

Using 2 matchfields has nothing to do with the issue. The problem is (or was) that the x relationship was starting with the Suppliers table - thus it was not visible from Orders until a supplier was selected.

[ArKay]

See if the attached helps.

Note:

You should be using a related table for order items, instead of repeating field. See an example here:

http://fmforums.com/forum/showpost.php?post/309136/

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.

[ArKay]

Using 2 matchfields has nothing to do with the issue. The problem is (or was) that the x relationship was starting with the Suppliers table - thus it was not visible from Orders until a supplier was selected.

Can you explain how the relationship should be set up?

[comment]

Can you explain how the relationship should be set up?

The way it's set up in the file I have attached to my previous post.

I don't have a table of items from which to select

It doesn't matter - the only difference is that the line item details must be entered manually instead of being looked up from the Items table.

[ArKay]

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.

[bruceR]

Repeating fields have an enormous range of problems. Among them: you can't report on them; you can't delete individual lines; you can't sort them; you can't summarize them; you can't have more rows than you specified; etc.

"and there would be no way to set up a portal as shown in the InvoicesDemo sample."

Not sure what makes you say that; but nope, not true.

Attached is a modified file that adds line items. Account calcs can be modified to work with this new configuration.

What's the supplier 1 supplier 2 business about?

Test2b.fp7.zip

Edited November 28, 2010 by Guest

[ArKay]

Repeating fields have an enormous range of problems. Among them: you can't report on them; you can't delete individual lines; you can't sort them; you can't summarize them; you can't have more rows than you specified; etc.

"and there would be no way to set up a portal as shown in the InvoicesDemo sample."

Not sure what makes you say that; but nope, not true.

Attached is a modified file that adds line items. Account calcs can be modified to work with this new configuration.

What's the supplier 1 supplier 2 business about?

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.

[bruceR]

You're imagining a problem that simply doesn't exist. The data can be imported from the repeating fields.

As previously mentioned, you have serious data structure issues. There is no problem accommodating any of your business goals and in fact it is done much more easily and more robustly by the methods you're hearing about from extremely experienced people.

[bruceR]

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.

Yes, you are mistaken. Did you actually look at the file I posted?

[ArKay]

You're imagining a problem that simply doesn't exist. The data can be imported from the repeating fields.

I'm not sure what you mean. What data from what repeating fields?

As previously mentioned, you have serious data structure issues. There is no problem accommodating any of your business goals and in fact it is done much more easily and more robustly by the methods you're hearing about from extremely experienced people.

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.

[ArKay]

Yes, you are mistaken. Did you actually look at the file I posted?

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.

[bruceR]

The Order ID field wasn't populated; also two fields were misplaced.

Test2b.fp7.zip

[ArKay]

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.

[bruceR]

The benefits have already been described. Yes, you will have to make changes. It's not that hard. It has been standard FileMaker design for the last 15 years.

Your question about the line items table is very difficult to understand. What is your question? What does it LOOK like the table is doing?

[ArKay]

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?

[bruceR]

The line item records ARE the line item records; not "almost", not sort of. A portal lets you "see" data from one table from a layout based on another table.

Each line item record has a unique record ID - as every record in your system should - and each one has an invoice ID which is the foreign key that links it to the primary key of the invoice it is associated with.

I created and deleted several line item records while messing around with the database.

[bruceR]

Perhaps the relational light bulb is beginning to glow?

Edited November 29, 2010 by Guest

[ArKay]

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.

Edited November 29, 2010 by Guest

[bruceR]

An important principal about primary keys for data relationships is that they should generally be meaningless and unchanging. I suggest you keep the auto-enter Order ID, you can even hide it; but use it as the basis for relations. You can continue to use your order number as a separate, visible, human-meaningful field.

The process of importing and splitting repeats is basically quite simple but you may need some help and may even benefit from somebody doing it for you. That is not a level of service I'm willing to offer for free. Others may or may not assist you with that.

[ArKay]

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.

[bruceR]

What are you doing with the calcs? Should be simpler to set up without repeats; though understanding when you are operating on related records is apparently a new concept for you.

"I've done this before, but only to separate records with repeating fields into individual records..."

And that is exactly what you are doing in this case.

[ArKay]

What are you doing with the calcs? Should be simpler to set up without repeats; though understanding when you are operating on related records is apparently a new concept for you.

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.

"I've done this before, but only to separate records with repeating fields into individual records..."

And that is exactly what you are doing in this case.

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.

[bruceR]

All the order records must have primary keys before you start. You can use the Replace function to generate them.

When importing repeat with split into invoice lines you must import the order ID.

I suggest you post copies here as you go.

Edited November 30, 2010 by Guest

[ArKay]

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.

[bruceR]

The work is nearly trivial; about one morning's work.

Your answer contradicts itself.

"I think I'd rather spend the time revising the current file by eliminating any repeating fields and converting them to portals"

[ArKay]

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