Use a Multikey Portal to Quickly Add Items to a Related Database - File Maker

On the CD-ROM: In the Conditional Portal Multikey folder.
Primary Database: Launch.fp5.
The Problem: You want a portal on one-half of a layout that you can click into to add items to an order that is in a portal on the other half of the screen. You want to avoid using drop-down lists or jumping between databases.
The Solution: Launch the Launch.fp5 database and look at the two portals at the bottom of the screen. The one on the left is a look into the products database (the stuff people can order from you). The portal on the right is a look into the line items database (stuff on this order). Above both portals are five global fields, cleverly labeled Type and Category1 through Category4. If you click Type and select one, you’ll see the product portal change. If you continue to select categories from left to right, the portal will show you a smaller and more specific list of your products, based on the selections that you make. How is it done? With a multikey portal.

What is a multikey? Basically, it is when you have more than one key per record on one side of a relationship and one or more keys per record on the other side of a relationship. In FileMaker, a multikey field is a field with more than one line of data in it, separated by carriage returns.

Setting It Up
The “key” to this multikey solution lies in the global fields mentioned above, the Type and Category fields. As you make selections in these fields, a hidden, unstored calculated field (text result), cCategories, is being built. cCategories is defined as:

gType & gCategory1 & gCategory2 & gCategory3 & gCategory4

As you can see, the field is simply a concatenation of the Type and Category fields all squeezed together. Once you’ve made a selection, the result of cCategories might look like this:


The Products database also contains a field called cCategories (stored, text result), but this one is defined a little differently:

This field is setting itself up to be a multikey. If you select just a Type and Category1 back in products, then the calculated line in the product database’s cCategories field (Type & Category1 & "¶") will allow you to see all records that match just that Type and Category1. If you make selections all the way down to Category4 in Invoice.fp5, you’ll only match and see this line in the products portal in Products.fp5:

Type &Category1 &Category2 &Category3 & Category4

Notice that there are little trash cans next to each category global field. This is to clear out the categories to select a new one or step back one level in the product categorization tree. Also notice that if you select, in this case, a home shopping (HS) product in the Type field and then select down to Category3 and then go back and reselect Catering (Cat) Type, you’ll see no products in the product portal. Why? Because there are no, for instance:

To clear out all categories, you would have the user click the furthest left trash can, which sets the portal to show all products. Or you could use a plug-in like Script Scheduler to automatically refresh the categories if you go back and accidentally select a Type/Category combination that has no products.

Use Smart Scripting to Easily Add Items to an Order Without Using the Keyboard
Take a look at the scripts that allow a user to add items to an order. First, how does someone add an item to an order? Once you’ve selected the categories you want, simply click the items’s name to add it to the order. Notice that, on a blank, new invoice, the item is instantly transferred to the first line of the right portal (LineItems.fp5). Also notice that the quantity defaults to one and that the line total and order subtotal auto calculate. Now, if you click another item, it will add that to the order as well, on line two.

Now try to add another of the first item you selected. While you might expect FileMaker to add a third line to your line items portal, it doesn’t. It simply ups the quantity of the already added item by one. Neat, yes?

Setting It Up
To set this up, you’ll need to create a script in line items first called Increase Quantity. This is for the part of the script that ups the quantity if the item’s already on the order. The script looks like:

Set Field ["Quantity", "Quantity+1"

Now go into Invoice.fp5 and create a script called “Add Item to Order”. Here’s what the script is doing:

#This loop simply drops the user off in the portal row they just edited/created. Loop

In keeping with the “no keyboard” philosophy, you can also delete a line item by clicking the trash can icon at the right of the line item portal. You can also increment and decrement the quantity for an item by clicking the up and down arrows next to the Quantity field.

Stacking Portal Fields Redux
Many people don’t realize that you can stack fields in a portal. This means that you can place a field into a portal and then place another field, a seemingly infinite number of fields in fact (bonus points to whoever finds out the limit), right on top of it! To some this may sound clever, but others will wonder, “Why do it? If one field’s under another and you can’t see it, then it’s worth dry dirt to me!”

Here’s a good reason to put stacked fields in a portal: limited space. Here’s another good reason: Don’t force users to see every possible bit of data available all at once. Give them the option to get more data on the screen if they want, without leaving the current layout.

Try it! In Invoice.fp5, click the little down arrow at the far right of the Products portal (the left portal). You’ll notice that, for most products, you are taken to a hidden field in the portal that shows you the full description of a product that you sell. There’s no reason to clutter up th portal with this field; it’s best hidden until a user wants to see it.

Setting It Up
Go into your Products.fp5 database and create the Description field, a stored text field. Next create a calculated field called cDescription (text result), defined as: Description

That’s not a typo! This field is defined to be exactly what is in the Description field.

Now go back into Invoice.fp5 and add the related Description field to the products portal. Arrange the field to be just beneath the Product Name field.

Now add a little arrow icon to the portal row and assign a script to it called Go to Product Description Field, defined as:

Go to Field [“Products_ by cCategories::cDescription”]

Now when you click the little arrow, you’ll be taken to the description for that product. And because you are viewing a calculated description instead of the original editable text field, a user won’t be able to edit the master product description, but can use it to get more info about a product for a customer on the fly.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

File Maker Topics