Calculated Fields in Forms for iPhone

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

Re: Calculated Fields in Forms for iPhone

Postby Brian_Houghton » Sun Apr 28, 2013 10:02 am

@Mikey,

Yes, you will need to use multiple calculated fields, similar to:

Calculated 1 = F1 + F2 + F3 + F4 + F5 + F6
Calculated 2 = F9 - (F8+F7)
Calculated 3 = Calculated 1 + Calculated 2

Please note that if you want to sum or average the records, you should check out the “run reports” option that you encounter when you tap the field name (in record list view).

Sent from my Nexus 7 using Tapatalk 2
Kind Regards,
Brian Houghton, DDH Software
Brian_Houghton
 
Posts: 2246
Joined: Wed May 20, 2009 8:30 am

Re: Calculated Fields in Forms for iPhone

Postby dhaupert » Tue Apr 30, 2013 8:23 am

Hi,

I'm sorry i didn't see your post here but did get your private message and responded. For the benefit of others, here is what I wrote:

Hi Mikey!

Thanks for writing. When you have a time elapsed that spans dates, simply subtracting the time is not enough as there are days between them that account for hours. If you are always going to have less than 24 hours total elapsed time, you can do a simpler calculation of this:

Field 1 Start Time
Field 2 End Time
Field 3 Elapsed Time (Calc) = End Time - Start time
Field 4 Adjusted Time (Calc) = Field 3 + 86400
Field 4 Conditional - if Field 3 is less than 0, output is Field 4, else output is Field 3

What this does is account for the number of seconds in a day (86400) and add that if the result is negative.

Now if you have the possibility of spanning multiple days and more than 24 hours, you can't use this trick! Here's how you'd do it instead:
Field 1: Start Date
Field 2: Start Time
Field 3: End Date
Field 4: End Time
Field 5: Days elapsed (Calc, output as Integer) = End Date - Start Date ---> Here we are subtracting to see how many days have passed
Field 6: Seconds for Days (calc, output as integer) = (Field 5 - 1 )* 86400 ---> Here we are converting the above to seconds so it can be used with the time. Subtracting one since if the difference between a time one day and a time the next day is not 24 hours + the time, but just the time differences accounted for below. (Make sure to use parenthesis when setting up the formula.
Field 7: Time to End 1st (calc, output as integer) 86400 - Start Time --> This is the number of hours from the start time to the end of that first day.
Field 8: Total Elapsed Time (calc, output as time) Field 6 + Field 7 + Field 4 --> The trickiest part to understand here. This is adding the time until the end of the first day (field 7) plus the elapsed seconds for the days that spanned between them (field 6) plus the number of seconds into the last day (the end time, field 4).
Field 9: In Hours (calc, output to 4 decimal places) Field 8 / 3600 --> The above was shown as a time which may be confusing when it's more than 24 hours, so this one calculates the total number of hours elapsed for billing type purposes.

I have created a sample database demonstrating this and uploaded this to the HanDBase Gallery for you to look at if need be. Here's the link:
http://www.ddhsoftware.com/gallery.html ... ecord=4774

To answer your second question, yes, if you have more than 5 operands in a calculation you can create a chain of calculations to compensate for that limit. For example, if you were adding fields 1-7 together, you could do:

Field 8: Sub 1 (Calc) = FIeld 1+ Field 2+Field3+Field 4+ Field 5
Field 9: Total (Calc) = Sub1 + Field 6 + Field 7

And you could then hide field 8 from view!

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

Previous

Return to HanDBase for iPhone and iPod touch

Who is online

Users browsing this forum: No registered users and 1 guest

cron