Relating files that don't have a related field

[jhotz]

I'm working on developing a point-of-sale and inventory tracking system for my church's reading room to handle all the inventory and sales. I've come across a few problems, and after searching the forum I was able to find my answer, so thanks to all.

Now I've come to a hurdle, and I'm not sure of the best way to handle it. What I need to do is to get information from 2 files to display in a 3rd file, but these files don't have fields that make sense to relate. Let me describe my file setup, and I'll clarify the problem at the end.

purchase (holds the basic information about a purchase, e.g. date, purchase total price)

purchaseItem (holds all the line items on a purchase)

item (holds all the items, i.e. the inventory)

transfers (holds non-purchase related transfers to the cash box, e.g. donations, and bank deposits when the cash box gets full)

cashBox (holds end-of-day reconciliation information, e.g. quantity of currency, amount of checks)

On the cashBox reconciliation form, I want to be able to display the total purchases for the day, and the total transfers for the day, so that a "total money into the cash box" amount can be displayed for reconciliation purposes.

So, my specific question is, what do I need to do to setup a relationship between cashBox and purchase in order to get this information, when there are no fields that the two files share in common? The same would be true between cashBox and transfers. I'm pretty sure I can build a query to sum the totals for a given date range once I can get access to the data, but it's getting access to it that I'm not sure about.

I've seen some databases that use a "constant" value to relate the files, but I'm not clear on this concept and how/if it would apply to my situation.

[ernst]

Hi,

Looking at your set of files I would think that for example cashBox could have one record for each day and a date field that could relate to a date field on purchaseitems, Same for tranfers.

Then you could easily use Sum(Relation::Price) calculated fields to display the daytotals of either transfers and purchases.

Regards,

Ernst

[jhotz]

Ernst,

It's always the simple solutions that I overlook. I guess I got so hung up on thinking that one dealt with items and one dealt with money that I completely missed the date field thing. You are right, I've got a date field in both files and I completely missed that I could link those together; now I've got something to play with over the weekend.

Thanks for the help!

Jonathan

[ernst]

Hey Jonathan,

I'm sure you're gonna have a nice weekend then...!

Regards,

Ernst

[jhotz]

Boy, if only all my problems were this easy to solve. It's working like a charm, and I've even got the field that shows the prior day's reconcilliation balance working as well. It uses a script to go to the prior record, copy the field, go to the next record (which is the current/new record), and paste the value. This seemed to be the only way because the "brought forward" field on the current record needs to get the value from the "closing balance" field on the prior record.

I can't seem to find a way to have a (new) record run a script when it's created, but I'll probably use a "button" for the end-of-day reconcilliation that will open the cashBox file, create a new record, and then run the script.

Thanks for help, Ernst

Jonathan

[kshell]

Jonathon,

Can you tell me about your script to copy a field from the prior record and paste it into a different field in the present record? I'm having a problem that this may work for. See the thread:

http://www.fmforums.com/threads/showflat.php?Cat=&Board=UBB16&Number=79681&fpart=&PHPSESSID=

for a brief description of my problem.

Thanks,'

kshell