My Line Items have Line Items Gaaaaaaa....

[BobWeaver]

...aaaaaaahhhhh!!

This is an invoicing system with an invoice file and a line items file. The invoice is printed from the line items file with a reverse relationship to the invoice file in order to display the invoice data etc.

Now here's the catch. Every product (from a product file) that is entered as a line item, will have several additional charge/credit items. Some will be optional, but most will be mandatory. For example a widget may have these subitems:

Set-up fee $10

Store rebate -$20

Shipping charge $5

Optional leather upholstery $30

There are hundreds of different possible subitems, and the user will be adding or deleting them to/from products regularly. I have set up a sub-LineItems file that is populated with the appropriate sub-items whenever a product is added to the LineItems file. That works fine, but I'm having a terrible time figuring out how to display a summary of these subitems on the invoice. What we want to do is show the line items, then a subtotal, and then totals of the applicable sub-items. So, if several products have a shipping charge, there will be only a single entry for the total shipping charges. Likewise a single entry for total setup charges etc. This is where I'm stuck. Even if I print the invoice from the sub-LineItems file, I can't think of any way to print all of the main line items together and then the summaries of the sub-items together.

All I can think of is to generate a subitems summary report in the sub-LineItems file, go to preview mode, copy, and paste the image onto the invoice. But, that is soooooo ugly. I hope there's a better way than this.

Any ideas would be greatly appreciated.

[geod]

A general suggestion might be to import all related records into a temporary file just for printing. I used to do this alot when working in DOS databases, I tried it recently in FM and it worked well in a standalone situation but encountered some problems in a multi-user situation where I could not quite get the controls set up to prevent two logged in users from running the process at the same time. It might be possible though. Anyway just a general suggestion. I will definitely be watching this thread to see what develops.

Graham

[BobWeaver]

Interesting thought. In any event, the user will have to execute a script in order to print an invoice. So, scripting of some data manipulation may be the solution.

Thanx for the suggestion.

[Garry Claridge]

I have something similar for a Quotes system. Where some quote-line-items will have a bill-of-materials (BOM). All views are through the Quotes file.

A Picking List layout exists and displayed via a button/script which writes the BOM items into the quote-line-items file. A concatenated/calculated key distinguishes between BOM items and non-BOM items. Hence, two different relationships/portals can display the relevent items for the quote.

Hope this is of interest.

Garry

[AndriesV]

Recently I've come across a similar situation where I want to hide multiple instance of a certain rows only.

What I've done is (explaining for your situation)

- create a serial number field in the sub-lineitems file

- create a selfjoin relationship in the sub-lineitems file

on the sub-lineitems-InvoiceNo

- create a relationship in the sub-lineitems file to the subitems file to lookup the Type like 'single' or 'multiple'

- create extra calculated fields in the sub-lineitems file like 'cItemDescription_Unique' =

if(LkUpItemType::Type='Single' and SerialNo > Min(selfjoin::SerialNo), "", ItemDescription) create similar fields for price and/or qty etc.

- Use these new fields in the portal for sub-lineitems on the invoice, have the summary fields that calculate the sub items invoice totals use the new calculated fields.

I couldn't create a similar Unique-only Invoice number Key field because it's not indexable having the relationship in the calculation. Using this cInvoiceNumber as the key for the sublineitems lookup would have been ideal to automatically hide all unwanted info in 1 go but .......If you find/know a way to do so I hope you'll share it with me.

Because of using the normal InvoiceNo in the relationship to the sub-linitems file, several seemingly empty sub-lineitem-portal rows for the will show up.

To avoid these 'empty' rows from showing up first in the portal you have to create an extra portal sort field in the sub-lineitems file that will assure the empty rows appear last.

Hope you can use this,

Andries

[BobWeaver]

Andries:

Coincidentally, that is exactly what I ended up doing. The trick to getting it to work is using auto-enter look-up fields.

I don't have time right now to provide the details because I'm out on a job-site at the moment and a very slow internet connection here. But, I can provide more details when I get back home in a few days.

[BobWeaver]

Okay, here is what I did.

In my SubLineItems file I have the following basic fields:

ItemCode - A number that identifies the type of item

ItemName - A description of the item that corresponds to the ItemCode

InvoiceNumber - The invoice that these sub-items belong to

ItemAmount - The actual dollar amount of the sub-line item

SerNo - An auto-enter serial number

Then I created a compound key field:

InvoiceItemCode = InvoiceNumber & ItemCode

... and created a self join relationship on the above field called sjInvoiceItem.

I then created an item total charge field with the following calculation:

ItemTotal = Sum(sjInvoiceItem::ItemAmount)

...which is then equal to the total amount for all subitems of a given item code for a given receipt. Essentially, this is the value that I want to display in the main file. But only once!

Then, I created an auto-enter calculation field:

IDInvoiceDisplay = case(Max(sjInvoiceItem::ItemAmount)>0,Choose(Min(sjInvoiceItem::SerNo)=SerNo,"",InvoiceNumber),"")

This field will be set equal to the Invoice number if it is the lowest serial number in the set of identical subitems for the given invoice, and the total amount is greater than $0.00. This is used as the key field in the relationship from the invoice file. So, there will be exactly one record matching the invoice number for a set of common subitems. The condition that they also must have a total amount greater than zero prevents a bunch of unnecessary zero charge items appearing on the report.

Finally, I created a relationship in the main invoice file from the InvoiceNumber field in that file to the IDInvoiceDisplay in the subitems file. I created a portal based on this relationship. In this portal I display the ItemName and ItemTotal.

While I don't like using a portal for printing, this seems to work okay.

One critical point to keep in mind is the calculation of IDInvoiceDisplay. Since it is an auto-enter calculation. It will not recalculate once it contains a value, and it may not calculate initially depending how the records are created. It works okay in my application because of the order in which the records are created. In other applications, it may require other means to force the calculation of this field. Or, use a script to enter the info in the field.

Kind of a messy operation, but it works.

[AndriesV]

Bob, thanks for the explanation,

Unfortunately I can't use the auto-enter calculation in my solution as the related fields can be changed directly by users. I don't want to script those changes at the moment (which would force correct updates but hamper the users with entry)

Writing for your situation: I added the InvoiceNumber to every related record and created calculated fields for every field I want to show up in the portal to only show data if Min(sjInvoiceItem::SerNo)=SerNo, plus 2 additional calc-fields, 1 for the correct sorting of the non-empty portal rows. (the duplicate rows having empty calculated fields would otherwise of course show first in a portal) and a 2nd calc-field that =1 for every unique related record so that I can sum this field as a count of unique sub-items...or use it as warning if the count exceeds the number of visual rows in the portal.

Quick question: I noticed you're using the Choose function in your calculation, is there a reason or advantage to use Choose instead of if/then?

Andries

[BobWeaver]

I used the Choose() function because an earlier version of the calculation was better suited to it than to an If() or Case(). In the final formula, Case() probably would have been better.