Invoicing 6 lines

[LaRetta]

I have a Invoice LineItem db. It contains PayorID (related to Payor db) and ClientID (related to Client db). Each month it will contain 2-3,000 lineitems. Our payors have two criteria: 1) Each client must be on a separate invoice and 2) Invoices can only have six lineitems.

I need to SET Invoice # and Line # through the series of records, to generate invoices. We have 18 payors and 850 current clients, so I thought of using a found set on a form in the Client db (Status Active) to find related invoices, perform the SetFields, then goto next record. Or a calc Payor/Client find combination in LineItem? I've even played with using Status(CurrentFoundCount) for the numbering loop ... as you can tell, I'm really stumped on how to find the unique Payor/Client records, then take that CurrentFoundCount and set the line numbers. All ideas will be appreciated!

[djgogi]

Define an relational value list "listCustomerInvoice" based on CustomerID & " " &InvoiceId (calculated field res text) kompound key as use values from field "ItemID" only related values from relationship "relCustomerInvoice".

Create an calc field

sixLineInvoice=Substitute(MiddleWords(ValueListItems(Status(CurrentFileName), listCustomerInvoice"), gStart, gSize)& "

[LaRetta]

Thanks for the information. It will take me a bit to figure this out. Upon reviewing it, I think I understand what you're telling me, but I'm having trouble understanding the first part.

Define a value list in LineItem (?) selecting Client db and relating ClientID with LineItem::ClientID ? Then you suggest I create a calc field ... should this be done in LineItem? Should it be text, stored or indexed? Also, Invoice numbers have not been assigned yet. I don't see how Invoice numbers are assigned in your solution. For every six items invoiced to a Payor, I need a new Invoice number assigned. When you say ItemID, do you mean the each record's unique serial number?

If you could say a bit more, I think I can give it a go! Thanks for your quick response!

[djgogi]

OK,

So let's say we are in Payor db and that you have relationship with Client db (in this way you can identify all clients for selected payor, no?) using PayorID in both file (if the rel between Payor and Client is many to many, than you'll need an join file but it does not change anything for the problem we are dealing with)

Now selecting specific client (thru choice list formated field for ex.) you could individuate every pending invoice for this client using relationship ClientID (selected above) in Payor db and ClientID in Invoice db.

Doing that you have gain access on every InvoiceID for selected Client for selected Payor.

So the chain is following, assuming:

that you have 2 global fields in Payor db:

gClientID and gClientInvoiceID

and that you have relationships:

relClient Payor-->Client payorID payorID

relClientInvoice Payor-->Invoices gClientID clientID

relInvoiceItems Payor-->InvoiceItem gClientInvoiceID invoiceID

and finaly value lists (all defined in Pyor db)

myClientList as use related values of field clientID from relationship relClient

myClientInvoicesList as use related values of field invoiceId from relationship relClientInvoice

myClientInvoiceItems as use related values of field itemID from relationship relInvoiceItems

Now the lines I wrote before

"Create an calc field

sixLineInvoice=Substitute(MiddleWords(ValueListItems(Status(CurrentFileName), listCustomerInvoice"), gStart, gSize)& "