elegant assign page# calc?

[uchujin]

there must be an easy way to do this, but I think I have stared at it for too long:

there are 2 files:

Invoices

Items

Invoices displays the Items (sold products) in a portal (related by invoice#). For printing, I have 2 Invoice layouts- A 1 page for invoices with 20 ir less items, and a 2 page for invoices with over 20 items. So far I have been using a script to set the page number for each item on invoices with over 20 items

in Items: (with number fields: Invoice#, Page#, and summary field: Count-a running count of Invoice#)

find invoice# (first invoice to be printed)

replace Page#(if (count <=20, 1,2))

This works fine, and lets me assign which page of the invoice each items print on, however, It must be run for each invoice being printed. No problem with a loop, but seems that there should be a more efficent method using a calc. field.

Is there a way to count by a sub summary (based on Invoice#) in a calc field, so I can set the page# and print hundreds of invoices at the same time without having to manipulate each one individually?

[neile]

It sounds like the invoice layouts are portals in the invoice file?

If the invoice layout is in the items file, you can use a sub-summary (when sorted by invoice number) to break the pages and total the invoice, and restart page numbers after every occurence. By printing from the items file, there is no limit to how many items can be on one invoice.

Use the header for your company info, the customer info, and the invoice number, date, and page number. Use the body for the item description, quantity, cost, and extended price. Use the sub summary for the invoice subtotal, taxes, freight, and invoice total.

To print a batch of invoices, make an 'invoice date' field in the items file and have the script search for all items with nothing in the invoice date field. Once it has found the set, have the script set the invoice date of the found set to the current date, sort by invoice number, then print the invoices.

Filemaker can automatically insert page numbers. Type two number sysmbols (##) where you want the page number to be.

[uchujin]

hmmmmm

of course. You are right, the invoices are printed from the invoice file with the items in a portal. I did it this way because...because...of a very good reason which I cant seem to recall right now. For 1 page invoices it works fine, but now I realize complicates matters for mult. page layouts. I will shift the invoice to print from the Items file- Thanks!!.

But regardless, the subsummary running count calc could come in handy in other ways- is it possible?

[RussBaker]

Neile's suggestion is the way to go. Its always best to print from the file where the data is stored, rather than use a portal for printing. Even Filemaker say its its not the way to go! You can make your invoices look a but more professional (and easier to track by total pages) if you use a format of "Page 1 of 4" format for your page numbers.

To do that, you need a global field g_TotalPages, then in the footer of your invoices, type in the text "Page ## of <<g_TotalPages>>" using the Insert MergeField menu item to do the last bit in the <<>>.

Then, as part of your script which prints the invoices, include

Go To Layout [The Printing Layuot]

Perform Script [The sorting routine needed for your sub-sumamry report]

Enter Preview Mode

Go To Record/Request/Page [Last]

SetField[g_TotalPages,Status(CurrentPageNumber)]

Enter Browse Mode

This will have stored the maximum number of pages in your invoice in the g_TotalPages field.

[uchujin]

I feel quite foolish as I already use the above script idea for all reports and lists I print for everything else- I seem to have contracted a mild case of myopia for the invoices however...

In atonement for wasting everyones time, let me offer a variation on that process which has the total pages# for each invoice set by calc, eliminating the extra script step and allowing mult. invoices to be printed after just sorting:

using 2 relationships, 1 from each file:

The relationship "items" is Invoice::Items by invoice#

The relationship "invoices" is Items::Invoices by invoice#

In Invoices (not Items!)-

Fieldname: TotalPages

Calc:

Case(

Count(items::desc) <= 25 , 1,

Count(items::desc) >= 26 and Count(items::desc) <= 50 , 2,

Count(items::desc) >= 51 and Count(items::desc) <= 75 , 3)

Now on header of printed invoice (Printed from Items file now...) put "Page ## of <<invoices::TotalPages>>"

n.b.-the count can actually be of anything (price, product#, customer name, even invoice#) that will get you 1 per item record

This arbitrarily assumes each page has a 25 item limit.