Select a Portal Row to Data-Enter a Line Item - File Maker

On the CD-ROM: In the Stack Select Dupe folder.
Primary Database: Solution.fp5, layout TWO.
The Problem:A customer makes and sells all kinds of pumps. On any given invoice, a customer orders ten pumps at a time, on average. For each pump, there are 30 fields worth of customer-specific data that needs to be collected to customize each pump to suit the customer’s needs (some pumps pump sulfuric acid; some pump poop).

How to enter this data into a FileMaker database? Well, most in-house developers create the invoice form layout and then just make a huge portal with gigantic rows, each of which is made to accommodate 40-plus fields! Your life really starts to suck as a data entry clerk, especially with large orders, as you scan the portals, field labels, and fields, trying to sort through the information.
The Solution: A better way to do this is to use a screen with a portal, through which you create, duplicate, and delete line items. Data is entered by creating or selecting an item in the portal, which then populates fields on the form view outside the portal in an organized way. (Please see Layout Two of the sample database to get an idea of what we’re talking about.) Now, all you have to do is press the New Item button, and a new item, highlighted in your list, appears. Then you type in the fields, as many as can fit on the whole screen, outside the portal without all kinds of crazy scrolling up and down a portal or right and left on a list view.

Setting It Up

1. First, in your Line Items database, create the following fields:
   • Item ID, an auto-entered serial number
   • Constant, a calculated field, the calc being 1
   • g Highlight, a global container field that you’ve dropped a big block of highlight color into
   • g Item ID, a global number field
   • c highlight, a calculated container field defined as:
If(Item ID = g Item ID, g highlight,””)

2. Now duplicate the two simple scripts, New Item and Delete Item, as they exist in the Support2 database, which is your Line Item database. Also, while you’re in your Line Item database, create a quick relationship to the Invoice database, called Invoices by Constant, using the Constant fields i both databases. Then go back to your Invoice database and create these fields:
   • g Invoice ID, a global number field
   • g Item ID, a global number field

Now, create some relationships in the Invoice database:

• Line Items by Invoice ID (allow creation of related records)
• Line Items by Constant
• Line Items by g Line Item ID to Line Item ID

Now you can put a simple portal on your form view using the “by Invoice ID” relationship, showing about ten rows. Put just the related Item [name] field in the portal and disallow entry to it; also, make it transparent.

3. Place the related c Highlight field beneath the Item field. Place all of your line item’s fields to the right, not in the portal fields like Item, Quantity, Color, Price using the “by g Line item ID to Line Item ID” relationship.
4. Create two scripts in the Invoices database called New Line Item and Select Line Item that look just like the ones in your SOLUTION.FP5 database.
5. Create a button on the layout called New Item and associate the New Line Item script to it. Make the c Highlight field a button and associate the Select Line Item script to it. Now when you click on an existing item, it will populate all the fields to the right so you can enter and edit them. When you click New Item, you can do the same with a new line item. You can also add a Delete Line Item button and scripts to the databases as in the example (click the little trash can).

That’s it! Now your users are ready to start adding items in an organized, pleasant-to-look-at screen. The only drawback with this method of creating line items in Invoices or Purchase Orders is that you can’t see all the items on one layout at the same time.

You can always create another display list layout, though. Also, if you don’t rely on on-screen buttons for navigation through these databases, a user could be looking at invoice 1001 with the line items for invoice 3001. When you fill in “g Line Item ID” in the Invoice database (the match field that tells you which line item to view to the right), it stays set until it is reset by your “Select Item” button, even if the user switches records using the book on the status bar, the keyboard, or the mouse wheel. This will confuse and befuddle people. Plan for it!