Conditional Field Based on Linked Field

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

Conditional Field Based on Linked Field

Postby RMittelman » Thu May 02, 2013 5:39 pm

This should be easy, but I can't figure it out.
I have a Budget_Items table that has:
Type (Text, either "Income" or "Expense")
Description (Text)
Amount (Float)
Inc (Conditional: If Type=Income, then Amount, else 0)
Exp (Conditional: If Type=Expense, then Amount, else 0)
RunningInc (Calculated: Inc+Previous RunningInc)
RunningExp (Calculated: Exp+Previous RunningExp)

Assuming DB is sorted Income items then Expense Items, the RunningInc and RunningExp show grand totals as far as each record. The last record shows the real grand totals of income and expense.

Now I created a parent table containing these items:
Type (Link, pass the Description field)
Description (Text, either "Income" or "Expense")

This new table has exactly 2 records. As you can guess, one is "Income" and the other is "Expense".

Then I modified the first table, and made the Type field Linked based on the new table. After going through each record and editing it, clicking the link button and choosing either Income or Expense from the "parent" table, all of the records now "look" like the Type column value is either "Income" or "Expense". I realize the type values are actually a unique value expressing the link properly.

Now the Inc and Exp fields in the first table no longer calculate. I can find no way of using the Type field's value in the condition. How can I have running totals of Income and Expenses if I can no longer evaluate the Type field?

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

Re: Conditional Field Based on Linked Field

Postby dhaupert » Fri May 03, 2013 8:27 am

Hi Ron,

Thanks for your post. My first question is why you changed this to be this way in the first place? I know there must be a reason and if I understand why it will help me offer you an alternative way of doing it that should work!
dhaupert
 
Posts: 4111
Joined: Tue May 26, 2009 11:51 am

Re: Conditional Field Based on Linked Field

Postby curtterp » Fri May 03, 2013 9:02 am

Instead of a linked field to another table for Income/Expense, I would have made that field a popup. The reason why I would have made it a popup is that there is only 2 conditions, income or expense.

You are looking for speed and accuracy because of the conditional field. A popup field gives you both when you have a small, finite set of values that you will use. Using a popup field, gives you the ability to use the conditional field.

The running field (runningInc and runningExp) do not need to be sorted to show the totals for each category.

I created a quick database that mimics what you have posted to show you that it works well.... please see the attached zip file.

I added a date field with a couple of view to show the current month, past month and year to date. There are also a couple of records to demonstrate the views with filters to show how powerful that can be.
Attachments
budget.zip
(1.88 KiB) Downloaded 67 times
Have a good day

Curt
I am not a DDHSoftware employee, just a long time HanDBase user.... from Palm to Windows Mobile to Android, to iOS. Thanks to DDH, the database files transferred to each platform without a problem.
curtterp
 
Posts: 679
Joined: Tue May 26, 2009 6:41 pm
Location: Northern IL

Re: Conditional Field Based on Linked Field

Postby RMittelman » Fri May 03, 2013 10:36 am

Thanks Dave and Curt, for answering my question. Why I designed as I did had a reason... Really. Here goes:

Originally I only had 1 database, Budget_Items. The Item Type field was indeed a popup with Income and Expense as the types. Like any budget needs, I wanted to have a grand total income and grand total expense available, so I could see if my budget was "working" for me (financially speaking). To do this, I tried something which blew up my database. I put a conditional "Inc" field in there which showed the amount field if the Item Type was "Income", else 0. Likewise, I put in a conditional "Exp" field which showed the amount field if the Item Type was "Expense", else 0. Then, I added "RunningInc" and "RunningExp" calculated fields to show running totals of income and expense (Inc+last RunningInc, Exp+last RunningExp). So far so good. Unfortunately, the running total expenses were only accurate on the last record as a "real" total expense. On prior records, it was just a running balance, like a checkbook balance.

To solve the problem, I did something which typically works in SQL-type DBs. I added TotalInc and TotalExp fields, each a relationship field, pointing to the same DB, running total fields, last record. Strangely enough, this worked just fine in my Windows desktop version, but messed up the database on my iPhone. Please see my other post, "Records Disappearing on Orientation Change". As Dave mentioned, I was in circular-reference-land and the iPhone app couldn't handle that.

To solve the issue, I decided to have a Budget DB with 2 records, Income and Expense as the field "Item Type". This field is indeed a popup type. Then I thought I could link to the Budget_Items DB so that I could see the income type items, and/or add more, and the same with expense type items. That's why I used Link / Linked fields for Item Type. Also, then I could add a relationship field to the Budget DB which would show the grand total of both Income and Expenses from the Budget_Items DB. The Budget_Items DB would work as-is (without the last 2 grand total fields), hopefully.

I think I am suffering from lack of HanDBase knowledge in my current design. Maybe I can't use both Link/Linked AND Relationship fields. Maybe I'm just doing something slightly wrong. The resulting 2 DBs should be very simple, relating to each other very similarly to an Orders or Invoices solution. You have a header table with order #, customer, etc. You have a detail table with order #, item #, qty, price, etc. The DBs are linked in such a way that you can pick an order then drill-down to the items on that order. You should also be able to display an order total in the order DB, using a relationship field, right? My Budget solution should be just as easy.

Maybe I just need pointers of how to build the DBs, where the Link/Linked fields should be, and where the relationship fields should be, etc.
I hope this explains my thinking, and why I've built the DBs as I have. Any suggestions?

Curt: The sample budget you sent is great. Thanks! It just doesn't give Total Income or Total Expenses. If those are obtainable in a view, without the circular-reference problem, I could go back to one DB only, and make a view for each total. I have many expense items, and hopefully don't want to scroll through all of them in order to see the grand total (which would be the same as running total on that last record).

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

Re: Conditional Field Based on Linked Field

Postby dhaupert » Fri May 03, 2013 11:57 am

Hi Ron,

Thanks for clarifying so well. To respond to the several points:

1. The desktop worked for the self referencing only in this special case- since the desktop doesn't recalculate when it follows a relationship field, the running total calculations are not updated, and thus normally the totals would be wrong for related fields. But in your case, you're not excluding any values in your relationship- it's a lookup of the entire database table so the calculation would be correct in this one case!
But normally while it wouldn't cause the disappearing records issue, the totals would be flat out wrong on the desktop, at least when referencing the same table with a subset of records!

2. You should be able to get around the linked not working in a conditional field by using a relationship field to bring over the value. You can take a look at how this gallery example is set up:
http://ddhsoftware.com/gallery.html?sho ... ecord=4775

The idea here is that the linked field won't have the Type field but a relationship would. And I think that should work for the relationship.


On a totally different note I believe you may have given me an idea for a way I can solve this circular reference issue. I am going to do a quick check of the relational field lookup chain and if the same database repeats a second time I will not redo the calculations. Unfortunately I can't do much with this right now as I'm doing Android development work, but I'm going to leave it as a todo list item for the next maintenance update and I'll let you know if it works!
dhaupert
 
Posts: 4111
Joined: Tue May 26, 2009 11:51 am

Re: Conditional Field Based on Linked Field

Postby RMittelman » Fri May 03, 2013 12:43 pm

Hi Dave,

Thanks so much for the answer. Being a developer, your detailed description in the gallery link lit a little lightbulb for me, and I figured out how to do the relationship field in the detail table without actually needing to download the example.

Since my link field in Budget is "Details" (I couldn't use "Item Type" itself for a link field, as that caused problem before) and my linked field in Budget_Items is "Item Type", I created a new field called "Item_Type" (note the underscore) of type Relationship. Related field in this DB is "Item Type" (the linked field). Other DB is "Budget". Other field name is "Details" (the link field). Show from other DB is "Item Type".

Sure enough, when I save everything, I see the "Item_Type" field in my list view containing "Income" and "Expense", and looks just like the first column ("Item Type"). I say "looks like" because we know the linked field is not "really" what you see in the list view, right?

I changed the "Inc Amt" conditional field to be: If Item_type = Income then use "Per Month" field, else use 0. Similarly the "Exp Amt" is built using the Item_type=Expense.
Unfortunately, this is not working, although it used to in the past, when the first field was Text type instead of linked.

Now I wonder if the new relationship field's position in the field list is causing it not to be recognized by my condition fields (even though I moved its visual position in the field list)? That would make sense, since the original "Item Type" field, the first field in the list, used to work as my condition (when it was a simple Text field).

Does HanDBase require fields that you use in a condition be higher up in the actual field list, or should moving it up in the order be enough? Depending on this answer, I may need to rebuild my entire database. What a pain. I'm not sure, but I think exporting, rebuilding, then importing works properly with regards to the link/linked fields. Last time I did it, I needed to visit each and every record in my newly rebuilt and imported detail table and tap the linked field button and re-choose the item from the parent table for the links to be recognized.

I think I'm getting there, Dave. Can you help with the last question, and advise what to do?
Ron Mittelman
RMittelman
 
Posts: 16
Joined: Mon Jun 01, 2009 6:17 pm

Re: Conditional Field Based on Linked Field

Postby dhaupert » Fri May 03, 2013 2:21 pm

Hi again,

The field name should not matter in HanDBase- we don't require unique names or anything specific to the name of the field, as we don't reference fields by their name but rather by their index, so you can name it whatever you'd like!

This should be working for you- just did a little test db to make sure relationship field values were used properly in Conditionals and it worked fine. It may be a field order issue but you don't need to redesign anything to test that- just change the field order. We use the field order for conditionals and calculations so that people can control the order of calculations without having to actually redesign databases!

Give it a try and if it doesn't work, send me an email (address below) and I'll forward you my test dbs or take a look at yours..
dhaupert
 
Posts: 4111
Joined: Tue May 26, 2009 11:51 am

Re: Conditional Field Based on Linked Field

Postby RMittelman » Fri May 03, 2013 4:54 pm

Hi Dave,

Thanks for all of the help. I don't know why I'm having these issues, but I read a few articles on your site and decided to switch to relationships instead of link/linked fields. This and some other re-designing seems to have solved my problems.

Main DB: Budget, 1 record. Has Text fields "Income" and "Expense" which contain those values also. Has relationship fields "Total Income" and "Total Expenses" which each use their respective fields mentioned before to do the relationship, and pull the running income or running expense from the detail table's last record. The Budget_Items DB seems to handle itself fine after removing the link/linked fields.

This solution is more than just basic budgeting. It's also a cash envelope management system. Each budget item has an indicator whether or not it uses envelopes. If it does, it also keeps track of how many of each denomination of cash to withdraw from bank every X number of weeks. This calculation is still a work in progress, so I've hard-coded every 4 weeks for withdrawals, although I did add a parameter for that in the parent DB. To be flexible, each budget item can have a different frequency with regard to its amount. IE, salary is x every 2 weeks, allowances are x every week, etc. There is a Budget_Freq table to calculate things so each budget item also displays the calculated monthly amount, which is used to indicate being over- or under-budget.

I'll send you the 3 DBs for your perusal via email, in case you're interested. Next step will be to multiply the calculated monthly amounts by a month factor so I can dynamically change the withdrawal amount if I change how often I want to go to the bank.

I can't thank you enough for your help. I've learned a lot about HanDBase!
Ron Mittelman
RMittelman
 
Posts: 16
Joined: Mon Jun 01, 2009 6:17 pm


Return to HanDBase for iPhone and iPod touch

Who is online

Users browsing this forum: Google [Bot] and 2 guests

cron