Calculate totals

For HanDBase related topics that are not specific to one platform.

Calculate totals

Postby patrick » Mon Jul 20, 2009 10:23 am

I have a wine database which lists details of each wine I now have or previously had in stock. This is linked to a locations database which sequentially numbers the slots in my wine racks where the wine is stored. Thus by linking to locations from a particular wine I can see how many bottles I have and where each one is stored. I would like to be able to create a "Quantity" field which automatically calculates how many bottles I have in stock of each wine so that I can see at a glance from the wines table how many bottles I have of each wine. Can this be done and if so how?
Thanks.
patrick
 
Posts: 2
Joined: Mon Jul 20, 2009 10:09 am

Re: Calculate totals

Postby DavidDevaneyJr » Mon Jul 20, 2009 12:55 pm

patrick wrote:I have a wine database which lists details of each wine I now have or previously had in stock. This is linked to a locations database which sequentially numbers the slots in my wine racks where the wine is stored. Thus by linking to locations from a particular wine I can see how many bottles I have and where each one is stored. I would like to be able to create a "Quantity" field which automatically calculates how many bottles I have in stock of each wine so that I can see at a glance from the wines table how many bottles I have of each wine. Can this be done and if so how?
Thanks.


This should indeed be able to be done. If you are indeed using the Link/Linked field combination to relate the two databases this should be pretty easy. You'll first need to add a calculated field to the Locations database that is a Running Total of the Wines in the locations database. That way you have the calculated value. Then back in the Wines database you would add a Relationship field, where the relationship is based off the Link and Linked fields in the two databases. The field you'd choose to Show from the other database would be that Running Total field, Quantity in your case. Then you'd choose to show the value from the "Last Matching Record".

I hope this helps.
Regards,
David Devaney, Jr.
DavidDevaneyJr
 
Posts: 134
Joined: Tue May 19, 2009 11:01 pm
Location: Lake Worth, FL

Re: Calculate totals

Postby patrick » Mon Jul 20, 2009 4:06 pm

David, thank you for your prompt reply to my post. Forgive my ignorance but I am fairly new to this. How would I set up a running total for each different wine. For example the locations could be as follows:
Location 1 wine A
Location 2 wine B
Location 3 wine B
Location 4 wine A
Location 5 wine C

If I set up a running total for all the wines in the Locations database and then follow your suggestion, won't I finish up (in the above example) with the answer 5 for wine C, instead of 1. So, don't I need to set up a running total for each separate wine? How can I do that?
Thanks.
patrick
 
Posts: 2
Joined: Mon Jul 20, 2009 10:09 am

Re: Calculate totals

Postby dhaupert » Wed Jul 22, 2009 9:59 am

Hi there,

David, thank you for your prompt reply to my post. Forgive my ignorance but I am fairly new to this. How would I set up a running total for each different wine. For example the locations could be as follows:
Location 1 wine A
Location 2 wine B
Location 3 wine B
Location 4 wine A
Location 5 wine C

If I set up a running total for all the wines in the Locations database and then follow your suggestion, won't I finish up (in the above example) with the answer 5 for wine C, instead of 1. So, don't I need to set up a running total for each separate wine? How can I do that?


Not ignorance, but great critical thinking! In actuality, running totals are based on the records that are currently within the filter range. When you follow a link for a particular wine, it will only show the records for that wine, and the total will be recalculated for that wine. What typically happens is that if you are entering data through the link, each set of wine bottles will be calculated for that relevant running total and so the relationship field will pull the correct value.

The only time that it won't will be if you opened up the locations database directly. This will require you to then tap the link in the wines database before checking the total for that value.

I hope this makes sense- it's kind of hard to put into words!
dhaupert
 
Posts: 4111
Joined: Tue May 26, 2009 11:51 am


Return to HanDBase General Discussion

Who is online

Users browsing this forum: No registered users and 2 guests

cron