I have set my retail point of sale solution to print a receipt for the customer. But I want to save the date, receipt number and the total to another file, to log the amount for each transaction and to keep a record of the sales for each day. I currently have a relationship via the "date" field, but only the first transaction in that date gets copied over.

I am thinking about setting a portal on the other file but can't figure out what relationship to use to map both files. Can anybody give me some pointers on how to do this?

Edited December 15, 200520 yr by Guest

I am not sure how your structure is setup, but you might want to use the recipt number rather than the date field.

I am assuming that you have at least 3 files setup (or tables in FM7+).

1. Customer (in your case receipt number, date, and i would think the total cal fields would belong here)

2. Items file/table. A list of all the menu items or selections to be added to the bill plus their prices.

3. Line Items file/table that stores each receipt number to all the items that were selected for that order.

The total calc field would then just be a sum of the relationship of the first table to the line items table keyed on ReceiptNum.

Once this is done, it would be fairly simple to write script that generates a report of all the transactions for any particular day.

Hello,

I have a price file that stores the upc/barcode, the description and price. I have a retail work file that I can scan the product barcode or upc code in to pull up the price from the price file, and there's also a daily log file in which I have only the fields for date, receipt number and total. Yes, I've thought about it and want to use the receipt number as the link between the retail work file and the daily log file. But the receipt number is not a sequential one. It is a combination of date, time and cashier number so it doesn't quite follow the one generated before it. I'm not quite sure what relationship to set so that the date, receipt number as well as price can be pulled from the retail work file and set into the daily log file.

If you have a relationship defined based in receipt number, and the relationship allows creation of related records, then the only thing your script has to do is set the date and amount fields. This will automatically generate the record.

Create a unique record number and use that as your key field instead of your receipt number then.

I am sorry if I am not grasping this. I have tried setting a relationship based on 'receipt number', but related records in the other file have not been created and I can't figure out why.

I have also tried the record number method. I set the record number to '1' and it maps to the other file, then I can set the date, price & receipt number fields. Since each line item is a record itself in the retail work file, I have tried setting a record number at the end when the receipt gets printed. I then tried creating a new record in the daily log file and incrementing the record number by one to differentiate it from the first receipt log, but it doesn't seem to work. I think I've been staring at this file too much so my mind seems to freeze up. If you can show me an example I would really appreciate it.

Ants take a look at this simple mockup solution to get some ideas.

I dont see why you need a seperate log file when you can just run reports from the original file. If you do, then please explain.

POS.zip

Thanks mr_vodka. I have seen your files and did get some ideas from them. I don't really need another file to hold the log but just like to have things "organized" - separated as a different file.