Creating records from multiple field information

[Bullfrog]

I'm stuck with my databases, and I'm fairly new to this, so please excuse my naivity.

This is my first post.

I have a database with about 15 records. This database refreshes on a monthly basis to produce about 15 invoices, that's all good and easy. Each record is an "order/invoice" for a "client" and has 10 fields for any of the many of products they can purchase. The 10 fields are named "product 1" "product 2" etc to 10. These fields then typically have data typed into them describing the product eg. "newspaper", and usually have from 1 to 6 different or similar products per order/record.

I am trying to develop a report that will show ALL the products ordered in the month (out of all the 15 invoices). This report then goes on to list the product's suppliers (related I guess to a supplier/product database) so I can reconcile further info when our suppliers send us their invoices, (like price and quanities).

The problem I'm facing is that everytime I try to sort records it will only sort the 15 records and provide 15 products, but there may be up to 35 different products ordered and in various quanities - and I want to show each and everyone of these products despite the repetition.

Can anyone give me a hint or direction?, I've been scanning all the threads but seem uncertain as to the plan of attack.

I do look forward to a reply.

Bullfrog

B)

[Oldfogey]

Welcome Bullfrog.

You need a separate related file/table for your items, using ClientID as the link.

You can then analyse the Items file to your heart's content.

In V6 there is a sample file called Time Billing which will give you the general idea. I thought there was an invoicing example somewhere but I can't find it.

[Bullfrog]

I appreciate your prompt reply Paul J.

You're a true gentleman.

However I don't know if I follow your hint. Yes, I have another file that contains ALL the possible products (900+), but what do you mean I need a "related table/file"? - would this be a table/file with only the products used in the current 15 records? and can this be generated automatically?

Regards,

Bullfrog

B)

[-Queue-]

You basically need to eliminate your productX fields and make each group of them an individual record in a related file. Your main file will have a portal using this relationship, with the relationship set to 'Allow creation of related records', so that you can enter data into a new line of the portal and have a new related record automatically created and linked to your invoice. I would suggest basing the relationship on invoice id. Then you can find all records you need in your related file and sort them as you like.

You can do a search for portal or relationship on this site and find dozens, if not hundreds, of threads on this topic, many with sample files and explicit instructions.