What would you suggest?

[bmoore]

I am getting an error when we try to print invoices, It was working with this:

FOR FIRST clients WHERE clients.client_id = w-client NO-LOCK:
  FOR EACH client-totals WHERE client-totals.client_invoice_no BEGINS (clients.invoice-prefix + string(w-fs-yr,"99"))
                         NO-LOCK BREAK BY client_invoice_no:
    IF LAST-OF(client-totals.client_invoice_no)
    THEN
    ASSIGN w-length = LENGTH(client-totals.client_invoice_no)
           w-cli = var(SUBSTRING(client-total.client_invoice_no,(w-length - 1),2))
           w-length = 0.

But this no longer works due to adding "CR" to the end of a few invoice #'s (to show credit)
So what I have been searching for is a way to break by Client Invoice Number without LAST-OF.
I can't use the FIRST of, because not all client_id's are consistent, some are two some are three characters.

I am still new to Progress, so I am sure there is something I have overlooked.(or not aware of)

I am running 9.1E. Please help point me in the right direction.
Thanks

 

[TomBascom]

It would help if your describe your table structure and the data relationships.

Right off the bat I'm puzzled by the first line. Do you have multiple clients with the same client_id? (I also generally dislike naming tables using the plural -- it makes code hard to read naturally.)

From a design perspective you are engaging in a major sin -- you have composite fields. An invoice number should sjust be an invoice number not a combination of invoice number, what looks like a 2 digit year, and type (presumably that is what "CR" is all about). Creating composite fields is going to lead to endless trouble. A field should be a single fact about the record. So in this case you should have 3 fields -- invoice number, invoice period, and invoice type. Undo that decision now. The longer you wait the worse it will get.

Once you properly break up the invoice number sorting, selection and break groups will all work naturally and this issue will go away. Until then any solution will be half-baked and will break the next time something unanticipated occurs in the data.

 

[tamhas]

As Tom says, based on the limited information here, it appears that the core problem is not fixing this for each, but in the underlying design. That is likely to be expensive to fix, although it could easily be worth it in the long run at lowered maintenance costs and increased flexibility.

For a short run alternative, you might consider scanning through a candidate set of records and building a temp-table containing both the decomposed fields and a key to the actual record. Then you can for each your way through the temp-table with appropriate breaks and fetch the records you need.