Viewing specific repetitions in a portal

[Jim Carr]

In a parts sales operation, I have an Invoices database with repeating fields for Item No, Description, Quantity ordered, and Price arranged straight across as line items. Customers typically order numerous line items per invoice.

In a related inventory control database, containing a tracking record for each part, I want to view in a portal only Invoice line items (especially quantities) matching a given Item no. I made a portal to the Invoices database, related by the matching item numbers. It successfully turns up all invoices mentioning a given item number. Non-repeating fields list fine: Date, Customer name, etc. Now the hard part: In the portal, from the repeating fields on the invoice, I need to view only repetitions of the line item fields corresponding to the part no. Of course, this part number turns up at a different line, or repetition, on every invoice where it appears.

Say an invoice lists some Item 99998s sold at repetition 7 of the Item No field. Another invoice shows some 99998s sold at repetition 3. The info I want to view in the inventory database portal, then, is in the repetitions 7 and 3 of the Quantity Ordered field.

I want to list those specific repetitions and only those repetitions. But I can't find any function which return to me the repetition number of the found item number.

How can I make Filemaker pick out and list only relevant line items (repetitions) to display in the inventory portal?

I realize that a relational database would be the ideal way to view this info, but I can hardly send out a separate invoice for every part on a customer's list. They HAVE to all be on one invoice. This means doing something with repeating fields.

Thanks for your help!

[LiveOak]

BAD ASSUMPTION!!! You don't have to send a separate invoice for each item in order to not use repeating fields. The big mistake is to use repeating fields to hold invoice line items when you also want to be able to generate summaries of items sold by part number.

If you have two files: 1) Invoices and 2) Line Items, you can enter the common information for an invoice in the Invoice file (customer name, date, shipping and billing addresses, etc.) and the information for each line item in the Line Items file via a portal from the Invoice file. To print and invoice, use the Line Items file with related common information from the Invoice file. This involves 1) finding line item records (use a go to related records, show only related records from the Invoice file) and 2) using a layout with a trailing summary to total the Invoice.

When you want to find out information by part number, you can sort and summarize the Line Items file by part number or view the Line Items file from the Part Number file using a relationship based upon part number. A portal in the Part Number file can display all transactions for a given part.

-bd

[Jim Carr]

LiveOak:

Your solution soounds exciting! I have three questions though.

1) You say I can "enter the information for each line item in the Line Items file via a portal from the Invoice file." I don't see how new records (line items) can be generated in the Line Items file without actually going into it and entering each Line item there as separate Line Items records. How can I accomplish this through the portal? Are you recommending the use of a script to prompt the user, collect the information and generate the new line item?

2)You say "To print and invoice, use the Line Items file with related common information from the Invoice file." If I am working directly in the portal on the invoice, no related common information (probably the invoice number) is being entered in each line item, unless I repetitively enter the invoice number into each line via an Invoice# field. Again, I must assume you are recommending a script to generate the line items?

3) To print an invoice, it seems to me that the user would only need to print the invoice with the portal showing realted line items, and a summary, rather than what you wrote, which confused me: "To print and invoice, use the Line Items file with related common information from the Invoice file. This involves 1) finding line item records (use a go to related records, show only related records from the Invoice file) and 2) using a layout with a trailing summary to total the Invoice."

I sure would prefer that the line Items file be transparent to the end user here, just letting him create and type into an invoice.

Regarding the difficulty of the project, I you're right, it is difficult, and although I have completed numerous other projects in Filemaker, this one has taxed me to my limits. Still I've already committed to it, so I hope you can see me through this one rough spot, or at least recommend where I might get some quick reading or education that will help me get it done. They are willing to do the adding and subtracting of parts themselves, but I think that's dangerous and want it built into the forms themselves. Me and my big mouth!

[LiveOak]

1) New line items can be entered directly into a portal. If the option "allow creation of related records" is selected for the relationship used for the portal, an extra blank portal row appears. When information is entered into this portal row, a record is automatically created in the related file. This isn't esoteric, it's in the manual. A second option is to use global fields in Invoice for line item information entry and use a Save/Enter/Submit/whatever button to trigger a script which runs an external script in Line Items which creates a record and copies the information from the related fields in Invoice.

2) Nope. You can create a reverse relationship using the same fields that link Invoice to Line Items to link Line Items to Invoice. This relationship can be used to display the customer information from the related Invoice record in the Line Items file.

3) The problem with using the portal to print is that is is of a fixed length. If your invoices range from one to 200 line items and span multiple pages, this won't work. Invoices should be printed from the Line Items file. The Line Items file is transparent to the end user. A single button in the Invoice file can trigger scripts which take care of all the operations necessary to print an invoice.

There is not a quick answer to coming up to speed for this one. It's like asking for some reading material to come up to speed for brain surgery, it's going to take some experience also. What we have discussed so far doesn't even fall into the category of what makes inventory control systems difficult. What we have discussed so far are pretty basic FM techniques used commonly for Invoice and Purchase Order type applications. If you are going to attack inventory control, I would recommend you have the current topics down cold. Dealing with the management of inventory quantities is generally much more complex. If you are on any sort of a schedule, I would recommend you get some consulting help to assist with this project.

-bd