Digital Capture
webOS Mobile Applications
My DataBank
HomeFeaturesAdvancedContact
Advanced Topics

This section provides detailed information about advanced topics that are of great interest to many users familiar in database and/or computer programming including users interested in learning about the extended features of My DataBank.

These advanced features within My DataBank have been implemented to enable power users more access to tools and functions necessary to help resolve more complex challenges within a mobile platform environment. If you have other inquiries related to advanced topics and ideas, please feel free to share or contact us.

  1. Basic Formula Guidelines
  2. Multiple Formulas
  3. Arithmetic Logic
  4. Conditional Logic
  5. Date Manipulation - fdate() function
  6. Calculating Date Values
  7. Default Query filters
  8. Lookup Fields
  9. Running Totals and Differences
  10. Title field override
Basic Formula Guidelines:
[top]

In order to access other advanced features within My DataBank, special commands and formulas have been implemented to program special instructions that can be used to manipulate data.

Formulas are created from the Category Setup screen which are entered and are recognized within any of the text fields (text1 - text5). Depending on the nature or behavior of the formula, data manipulation is rendered or activated before entering the Item Detail screen, after pressing the update button from the Item Detail screen, or is displayed as an option button from the Item List screen.

Below are the general guidelines that will help you get started in using formulas:

  1. When implementing Queries, Formulas, and Conditions, the first character in the Text Field title must be the equal character ("=") followed by a space.

  2. Queries, Formulas, and Conditional Checking statements can be entered in any Text Field Title.

  3. Each operand must be divided by the space character (" ") and must follow the command syntax.

  4. Multiple formulas can be included within a Text Field Title by using the semicolon character as an operand (";").

  5. Conditional checking or comparing dates are stored as values represented in milliseconds. However, the fdate function can be used to facilitate date value manipulation.

  6. Field names are case-sensitive and are lowercase.


Multiple Formulas
[top]

My DataBank can process multiple formulas within a single text field through the use of semicolons. Below is an example that will process 3 separate formulas sequentially:

= int1 = int1 * 3; int2 = int1 + 5; int3 = int1 + int2;


By entering and updating the value of int1 with 2, the final field values based on the multiple formulas above would automatically update to the field values with the following results:

int1 = 6
int2 = 11
int3 = 17


Below is an example that uses a bit1 checkbox with 2 formula conditions that are processed sequentially.

= if bit1 == true then int1 = int2; if bit1 == false then int1 = int2 * 3.2808399;

Arithmetic Logic
[top]

syntax
[dest field] = [field] [arithmetic operator] [field/value]

arithmetic operatorlogic
+addition
-subtraction
*multiplication
/division

Assigning default field values or initializing field values:
[top]

Basic formula calculations can be used to initialize values or manipulate values.

For example (Assuming Int1's Field title is "Total Cost", and Int2's Field title is "Units", and Int3's Field title is "Price"), the following value entered as the Field Title for text5 could automatically generate the Total Cost after pressing on the update button from the Item Detail Screen:

= int1 = int2 * int3


The following example multiplies the value stored in int2 by -4 and placing the result in int1:

= int1 = int2 * -4


Conditional Logic
[top]

Conditional statements can be used for validation before performing a basic formula calculation.

syntax
if [field] [condition] [field/value] then [arithmetic logic]

conditionlogic
==equals
!=does not equal
>greater than
<less than

The following example below validates whether bit1 is false before executing the subsequent arithmetic logic.

= if bit1 == false then int1 = int2 * int3


The following example checks to see if the title for an item is untitled before assigning a custom default title value.

= if name == 'Untitled' then name = 'New Title Default'


The following example checks to see if the date1 value for an item is blank before assigning the current date and time for the date1 value.

= if date1 == '' then date1 = now


  • bit field formulas with condition checking function properly only when using true or false as shown above (without apostrophes). However, SQL string condition checking require 'true' and 'false' wrapped with apostrophes. Using 0 or 1 are unrecognized.

fdate function for manipulating date values
[top]


syntax
fdate([date field]=[format expression])

format expressionoutput description
%dday of month in 2 digit format: 01 - 31
%mmonth in 2 digit format: 01 - 12
%Yyear in 4 digit format: 0000-9999
%Hhour in 2 digit format: 00 - 24
%Mminute in 2 digit format: 00 - 59
%Sseconds in 2 digit format: 00 - 59
%wday of week in 1 digit format: 0 - 6, 0 = sunday
%Wweek of year in 2 digit format: 00 - 53
%jday of year in 3 digit format: 000 - 366

example:

Using fdate() within the ADVANCED SORT ORDER entry to control date order:


fdate(date1=%Y%m%d)


This example demonstrates how the advanced sort order entry can be used to sort records by date value followed by the title (name) value.

Calculating Date Values
[top]


Date values are stored in the database as the number of milliseconds since the midnight of January 1, 1970. To calculate the number of days between two dates, the following set of formulas can be used:

= int1 = date2 - date1; int1 = int1 / 86400000


To calculate the number of days between a date value against the current date, the following set of formulas can be used:

= int1 = now - date1; int1 = int1 / 86400000


To calculate the number of hours between two dates, the following set of formulas can be used. (This assumes date2 is a future value):

= int1 = date2 - date1; int1 = int1 / 3600000


With the ability to calculate date values, you can employ formulas for your categories to help you determine future dates. A gardener for example, could calculate the best date for harvesting their crops after it has been planted.

Thus allowing us to answer questions such as: If today is the first day of March and I've just planted some beans, what date would they be ready for harvesting if it takes 52 days?

So to calculate a future date (date2) by a given number of days (int1) starting from a given start date (date1), the following set of formulas can be used:

= date2 = int1 * 86400000; date2 = date2 + date1;


To help you determine the correct divisor/multiplier to acquire specific parts of a date, you may refer to the following table:

TimeEquivalenceDivisor/Multiplier
Second1000 Milliseconds = 1 Second1000
Minute60 Seconds = 1 Minute60000
Hour60 Minutes = 1 Hour3600000
Day24 Hours = 1 Day86400000

Default Query filters
[top]

Generally, all items for any category appear in the Item List screen. In some cases, you may prefer to hide certain items based on specific conditions. Whether it is date driven or on/off bit field controlled, the command below can filter your list down to maneagable numbers without having to delete any information that may be valuable for historical reference.

Query filtering in the form of an SQL statement can be used to hide records from your list. For example (Assuming Date1's Field title is "Completed Date"), if you wanted to only view pending items in your list and hide items that have been completed, the following value entered as the Field Title for text5 could be used:

syntax
where [sql condition]

Examples:

= where date1 = ''


= where fdate(date1=%m%d%Y) = '01022010'


= where bit1 = 'true'


A common need to control and hide items from a list can be done by using a checkbox or bit field. With a bit field named 'Hide', the following formula can be used:
= where bit1 != 'true'


Notice the following examples combine two conditions using the and boolean operator (or & not are also supported):

= where date1 >= date2 and date1 <= date3


= where fdate(date1=%Y%m%d) >= '20101001' and fdate(date1=%Y%m%d) <= '20101201'


Lookup Fields
[top]


syntax
lookup [dest field] [mode] [source field] [categorytypeid] [sql options]

parametervalues
moderestricted = restrict values within list
open = allow values outside of list
categorytypeidcurrent = use current categorytypeid
(value) = use specified categorytypeid
sqloptionsorder by text1

Example:

= lookup text1 restrict text2 current


This example illustrates how lookup sources can be derived from the title values from other categories.
= lookup text1 restrict name 13

Running Totals and Differences
[top]

Running totals and differences can be used to automatically populate fields with a running total or running difference across a list of items.

This is a very useful function for account balance, miles per gallon, and other related applications that require running value calculations. Upon including this command function, a button will appear in the Item List screen that will allow users to execute this process manually.

syntax
runtotal [source integer field] [destination integer field*] [order field]
rundiff [source integer field] [destination integer field*] [order field]

* Caution: This function will overwrite any values designated for the destination integer field. Make sure the destination field is reserved for dynamically calculated values.


Example:

= runtotal int1 int2 date1


= rundiff int1 int2 date1


  • A valid order field value is required for this function to calculate values in proper sequence.

This example shows 4 records with a running total calculated from an arbitrary list costing in the order of $1, $2, $3, $4.

Title Override
[top]

The first column that appears in the Item List screen is usually the Title field (or Name field internally). To override this column with another field, the following command can be used for reassignment.

syntax
titleoverride [replacement field]

The example below, sets the first column to appear with values from the date1 field as the new default.

Example:

= titleoverride date1


This example illustrates how the titleoverride command can be used to replace the title column with date values from the date1 field.