Form Status Question

[dvannoy]

I currently have a form that tells us how much we received on a line item bases per document..

each record is a line item that has a status...based on the status of that line item it is then forwarded to our billing dept..(per line item)...They will not bill the customer until the entire document is completed...there may be several line items that have changed status to be billed but cannot be billed until the whole document is complete..

is there a way to do a record count on each document and not let the billing dept see that document until its be completed...see they may complete the line items at different times, so according to them they have released that line item and should not see it again...

any help or suggestions would be appreciated

Thanks

 

[JerryDennison]

The best way to do this is management by exception. That is, only give the billing department those documents that are billable to begin with. You can do this using an unmatched query. Create a query based on your table of line items. All you need on the QBE grid is the link to the document table and the status field. Set the criteria of the status field to whatever it is that keeps the document unbillable. Next make this a totals query where you Group By the document id and Where the status field. This will give you a list of all documents that have at least one line item that matches your status criteria. Next create another query based on your document table(you can also add the line item table if you wish) to this query also add the query just created. Drag a join line between the document id of the document table to the document id of the query. Double click this line and change the join type to include all fields from the document table and only those matching from the query. Place all the fields you want from the document table(and line item if needed) then make sure you also add the document id field from the query. Set the criteria of this field to Is Null. This should give you what you're looking for.

 

[dvannoy]

i tryed it and cannot seem to get it....Im not ure i understand you...I tryed something similar to this already and cannot seem to make it work....I also tryed doing a count on the status field as well...maybe if you have an example of some sort i can look at?
I would appreciate it....

Thanks for your help..

 

[dvannoy]

OK i finally got it...But wont this still put these docs in the billing status even though all the line items may not be ready to be billed???

seei was trying to create something that new...the number of records per doc# and until the status was equal to be billed thats when i wanted the billing dept to see it...see they need to see the whole doc at once...cause they dont bill line item to line item...yjeu bill the whole document at once...

i'm not sure this can be done..

Thanks

 

[JerryDennison]

Of course it can be done. All I've given you is a tool to insure that only the documents that are ready to be billed show up in the query. You can include the line items(as stated above) as part of this query. In other words, you can display the document and all the line items associated with it for only those documents that are ready to be billed.