Newbie Questions - First Databases

Discussion of the version of HanDBase that runs on the iPhone and iPod touch devices. This includes the synchronization conduits as well.

Newbie Questions - First Databases

Postby RMittelman » Wed Jan 09, 2013 11:42 am

As mentioned, I'm a newbie to HanDBase, but have worked with other RDBMS's.

I'm developing a monthly budget DB, and need a bit of direction.

First: Is it possible to have a "hidden" database, to use for link and/or popup sources, but not visible in file cabinet? This could be used for various parameters, but wouldn't normally be needed to be accessed, so shouldn't be visible in file cabinet.

Second: Presently, my budget is in Excel. I have columns for type (income/expense), budget category, amount and frequency (weekly, bimonthly, etc.). Then I have a column for Monthly Average. This is calculated by multiplying/dividing the frequency by a factor, which I store in an area of the spreadsheet, and access using VLookup. This seems a candidate for DBLookup field, but would require a second database (hence first question). Presently, I'm using a popup with the choices listed (weekly, biweekly, every 4 weeks, etc.). This looks ok, but doesn't give me a way to calculate the monthly average.

Third: As with any budget, I'd like to see if I'm exceeding my income as I add or change new items. So I guess I need to somehow show total income and total expenses and difference between those. Should I use a report? A filter? If I invest in forms, could the form show the running total of income and expense items as I was editing or viewing any of those items?

Thanks in advance for any help...
RMittelman
Ron Mittelman
RMittelman
 
Posts: 16
Joined: Mon Jun 01, 2009 6:17 pm

Re: Newbie Questions - First Databases

Postby dhaupert » Wed Jan 09, 2013 2:27 pm

Hi there,

Welcome to the forums! It isn't possible to hide a file from the File Cabinet completely, because that's the place you would ultimately need to delete or access it from- once hidden there, there wouldn't be a way to access it to delete it, open it, etc. But I understand your point- I have a set of databases I use daily and only have a single entry point. So here is what I do:

I create a category for the main entry point and put the main database as that category.
I create a category for the sub-tables and put all the rest in there.

In my Choose Database screen I only keep open the folder tab for the main category, thus the others are hidden away from my view.

I actually do this for a few different databases, and typically only keep 2 categories open so that I can see those main databases on a single screen without scrolling.

Now to your other questions..

Second: Presently, my budget is in Excel. I have columns for type (income/expense), budget category, amount and frequency (weekly, bimonthly, etc.). Then I have a column for Monthly Average. This is calculated by multiplying/dividing the frequency by a factor, which I store in an area of the spreadsheet, and access using VLookup. This seems a candidate for DBLookup field, but would require a second database (hence first question). Presently, I'm using a popup with the choices listed (weekly, biweekly, every 4 weeks, etc.). This looks ok, but doesn't give me a way to calculate the monthly average.


You could keep things the way you have them and use a chain of hidden conditional fields to come up with those factors. For example, fields like this:

Field 10: If frequency is equal to weekly, then output is 1
Field 11: if frequency is equal to biweekly, then output is 2, else output is Field 10
Field 12: if frequency is equal to every 4 weeks, then output is 3, else output is field 11.

(Just used 1,2,and 3 as sample values for this example)
The final field in this series would be the one that would show the actual value you need to do your calculations. That could be hidden or shown as needed.

Otherwise, indeed you can set up a DB Popup table and the other table would have each entry with it's matching factor as two fields. Then using the group feature you can bring both items over when selected. Either option will work!



Third: As with any budget, I'd like to see if I'm exceeding my income as I add or change new items. So I guess I need to somehow show total income and total expenses and difference between those. Should I use a report? A filter? If I invest in forms, could the form show the running total of income and expense items as I was editing or viewing any of those items?


While I do love using forms on my databases, it's not necessary for what you need. The calculation field can do running totals and so you can keep a running total of any value you'd need. Take a look at the checkbook sample included as this does basically the same thing. Let me know if you have any followup questions!
dhaupert
 
Posts: 4111
Joined: Tue May 26, 2009 11:51 am

Re: Newbie Questions - First Databases

Postby RMittelman » Wed Jan 09, 2013 3:45 pm

Dave, thanks for your quick response. I will follow your suggestion for my first question.

Second: I think the latter suggestion would work better than the former, as I actually have quite a few more frequencies, and the former solution seems to require a separate field for each possible frequency.

Third: The checkbook example, using running total, works perfect for a checkbook, where you want the prior balance +/- the new amount. What I am after is a total of ALL income-type amounts, a total of ALL expense-type amounts, and then a calculation of the difference (either plus or minus).

I notice in the Time Billing example, you can do something like that with a linked DB which contains the details related to the record in the main DB. It allows you to select a running total field, but specify it's from the last record in the subset of details. In my case, I don't have "detail" DB, it's the same DB as I am working on. Does HDB allow you to link to the same DB to get this effect, or would that cause circular-reference errors? Otherwise, I don't know how you would attain total income, total expenses and difference.

It's possible I could do this with a report or filter of some type. If the linking method won't work, I would not mind tapping a button or heading or something to flash a report showing total income, expenses, and difference. This would be a small price to pay. Which method would you suggest?

Thanks...
Ron Mittelman
RMittelman
 
Posts: 16
Joined: Mon Jun 01, 2009 6:17 pm

Re: Newbie Questions - First Databases

Postby dhaupert » Wed Jan 09, 2013 3:55 pm

Hi Ron,

Thanks for your quick response. Regarding the third question, the checkbook does indeed only have a single field, but you can do this for multiple fields using multiple running totals. For example:

Field 1: Expense Amount
Field 2: Income amount
Field 3: Running Expense = calculation which is Field 1 + Previous Field 3
Field 4: Running Income = calculation which is Field 2 + Previous Field 4
Field 5: Difference = Field 4 - Field 3

Now the difference field should reflect the difference between all the expenses and incomes to that point. Does that do the trick?
dhaupert
 
Posts: 4111
Joined: Tue May 26, 2009 11:51 am

Re: Newbie Questions - First Databases

Postby RMittelman » Wed Jan 09, 2013 4:38 pm

Hi Dave,

Sadly, this doesn't fit the requirement. Think like a bean-counter for a minute. You add income items to your budget. The total income at the bottom reflects all of them, even if you go to a prior item and edit it. The total income is not related to the records up to and including the current one, it is the total of all income-type records.

Likewise for expenses. You may have 50 different expense items (groceries, rent, utilities....). Whether you are entering a new one at the end of the list, or tweaking the amount of an earlier item, you want the total expenses amount to be the total of ALL expenses. Only that way can you obtain how much over- or under-budget you are.

This is why I mentioned the Time Billing example, which uses a completely different method of getting the total. It actually looks at the detail table, gets the detail records related to the current header record, and obtains the running total from the LAST of those detail records in the subset. It seems we would need some technique like this, except we don't have a detail table. Can you use DBPopup with the same DB without causing errors? If so, we could use the same budget DB, using Item Type (income/expense) as the first field, thus getting all income- or expense-type records. If I added a running total, and specified LAST record in the relationship, that may work. I don't know if HDB allows this, however. In standard SQL databases, it's easy to join a table to itself...

Failing all of that, can you suggest a way to get an easy report showing total of income amounts and total of expense amounts?
Thanks...
Ron Mittelman
RMittelman
 
Posts: 16
Joined: Mon Jun 01, 2009 6:17 pm

Re: Newbie Questions - First Databases

Postby RMittelman » Wed Jan 09, 2013 6:31 pm

Update:
I am *almost* able to achieve my goal as follows:
Added "Inc" field, conditional: If "Item Type" field = "1-Income" (a popup value) result is "Per Month" (a calculated field), else value 0.
Added "Exp" field, conditional: If "Item Type" field <> "1-Income", result is "Per Month" field, else value 0.
(thus, all income records have monthly value in "Per Month" AND "Inc", with "Exp"=0, while all expense records have monthly value in "Per Month" AND "Exp", with "Inc"=0).
Added "TtlInc" field, calculated: Current "Inc" + prior "TtlInc" (ie: running total income).
Added "TtlExp" field, calculated: Current "Exp" + prior "TtlExp" (ie: running total expense).
Added "Income" field, relationship: Other DB = MyBudget (same DB as this), fields both = "Item Type", show "TtlInc" from last matching record.
Added "Expense" field, relationship: Other DB = MyBudget (same DB as this), fields both = "Item Type", show "TtlExp" from last matching record.

This proves I can get relationship from same table, thus filtering records to get either total income or total expenses.
The problem with this is: for any given category I get something like this:

Category -- Per Month -- Income -- Expense
-----------------------------------------
Groceries -- 86.67 -- 0.00 -- 650.01

(dashes are attempt to preserve columns. Can't figure out spacing/font on this forum).

What is expected is to have something like "1000.00" under the "Income" heading.
Note, I'm only showing 1 record in this example. I actually have an income record with 1000 value, and 3 expense records totaling 650.01.
So my data is correct, but I'm not properly gathering the "Inc" for the expense records, or the "Exp" for the income records.
I think I need to revert to your earlier example where each record contains an income amt and exp amt field which I properly type the value into, rather than the conditional fields. Then I can simply run the totals as you suggested, right?

Another issue, which I just noticed, but don't know if it's related to my efforts at totaling:
I open the database and see all 4 records, the 1 income and the 3 expenses.
I turn the iPhone sideways, and I only see the 3 expense records, the income record is missing. The filter button at bottom says Default View (3/4).
If I tap "Home", then re-enter database (still in landscape mode) all records are there.
If I rotate to portrait mode, back to 3 records again.
This is reset back to normal if I either exit the DB or change the view.

What do you think is up?
Also, if I sort by type and category a "?" appears below the heading, but above the records. I'm very confused!

Thanks...
Ron Mittelman
RMittelman
 
Posts: 16
Joined: Mon Jun 01, 2009 6:17 pm

Re: Newbie Questions - First Databases

Postby bestangeland » Sat Nov 16, 2013 8:21 pm

I just bought HanDBase for my iPhone 5s and want to use it to replace Bento, which is being discontinued. How do I transfer 750 entries in Bento to HanDBase? I couldn't find a help option on HanDBase or a users manual.
I have Bento 3 running on my iMac (System 10.6.8) containing a backup of this database, and can generate an Excel or CSV file there.

Is there a version of HanDBase that would run on my iMac that I could sync with my iPhone?

Thanks,
bestangeland
bestangeland
 
Posts: 1
Joined: Sat Nov 16, 2013 7:52 pm

Re: Newbie Questions - First Databases

Postby dhaupert » Mon Nov 18, 2013 5:01 pm

Hi there,

Thanks for your post. We do have a Mac Desktop available as well as the iOS version. Unlike Bento, where the desktop was their main platform, we have always had a far more capable mobile application than our desktop, which is more of a companion import/export/synchronization tool. But more info is available here:

http://ddhsoftware.com/handbase_mac.html

You don't need the desktop to get your databases from Bento into HanDBase though- here's how you can do it:

Use the Bento desktop to export your database to CSV format. Make sure to include the field names as the first line.
Use the Desktop Connect feature of HanDBase so that you can connect to it from your desktop web browser. See the manual here for more info:

http://www.ddhsoftware.com/docs/HanDBas ... rGuide.pdf

In the Desktop Connect page you see on the computer, there is an option to import a CSV file to HanDBase format. Using this option you can specify a database name, browse to the file you exported from Bento and then import it- the database will be created for you. You can then modify and tweak the design using the iPhone version.

Hope this helps!
dhaupert
 
Posts: 4111
Joined: Tue May 26, 2009 11:51 am


Return to HanDBase for iPhone and iPod touch

Who is online

Users browsing this forum: No registered users and 1 guest

cron