PocketMoney 4.0 Data Formats (c) 2006 by Catamount Software =========================================================== Several users/linux developers have requested the PocketMoney database format so they can create support on Linux for PocketMoney. Below are the record formats for the PM-Transaction record, PM-Account record, and PM-Lookups record. I'm copying the record structs out of my header files. The record structs are for the unpacked records. The packed records (which is what is stored in the database) looked exactly the same except the char* are the packed strings. Since, you are all developers that need to use this I'm sure you'll happy with the documentation or lack there of. ;-) !!! Please let me know if you create anything based on this info. It could change in the future and I'd like to try to keep you informed of any changes. I'm going to link the old versions of this file so you can see the changes. This file will only contain the latest database structures. Version 3.0 formats are available http://www.catamount.com/PalmApps/PocketMoneyPalmDBFormat30.txt -- Hardy Macia -------------------------------------------------------------------------------- General Database Info 4.0 -------------------------------------------------------------------------------- #define appFileCreator 'Pkt$' #define appVersionNum 0x40 #define kTransactionDBType 'Data' #define kTransactionDBName "PM-Transactions" #define kAccountDBType 'Dat2' #define kAccountDBName "PM-Accounts" #define kFilterDBType 'Dat4' #define kFilterDBName "PM-Filters" #define kLookupDBType 'Dat5' #define kLookupDBName "PM-Lookups" #define kRecurringDBType 'Dat6' #define kRecurringDBName "PM-Recurring" -------------------------------------------------------------------------------- TransactionDB Info -------------------------------------------------------------------------------- TransactionDB is the main database. The only real gotcha is that transfers are stored as two parts (transfer from and tranfer to). The transfer from is a positive amount and transfer to is a negative amount. The date and amount must be the same for both halfs of the transfer and the payee & account must be compliments of each other in each half of the transfer. // Define Transaction Types #define withdrawal 0 #define deposit 1 #define transfer 2 #define allTransactions 3 #define repeatingTransactions 4 typedef UInt8 TransactionType; typedef UInt32 vDate; #define kMaxSplits 0xFF // the greatest number of splits that PocketMoney current supports typedef struct { double amount; // amount double xrate; // exchange rate (greater than 0) UInt32 internalTransferLinkUID; // unused UInt32 externalBudgetLinkUID; // link to QuikBudget record UInt32 externalAutoLinkUID; // unused char *category; // category char *className; // class char *memo; // description char *transferToAccountName; // if a transfer it will hold // the account name // if not a transger it will // be null char *currency; // currency } vSplit, * vSplitPtr; typedef struct { UInt8 recordVersion; // appVersionNum Boolean cleared; // cleared vDate lastModTime; // last time record was modified vDate date; // transaction date double subTotal; // amount (will be negative for // withdraws and positive for // deposits. Transfers will be -/+ // depending on if it is a transfer // to or a transfer from.) double runningBalance; // for future use char *account; // account char *payee; // payee (or an account name if // transaction type is a transfer) char *chkNum; // check number or id UInt8 numSplits; // up to 255 splits vSplit *splits; // split items } TransactionDBRecordType, * TransactionDBRecordPtr -------------------------------------------------------------------------------- AccountDB Info -------------------------------------------------------------------------------- You must create an account record in the account db if you download a new account into PocketMoney. Otherwise PocketMoney won't know about the account and therefore won't display it in the Account Balance screen. // Define Account Types #define typeBank 0 #define typeCash 1 #define typeCreditCard 2 #define typeAsset 3 #define typeLiability 4 typedef UInt8 AccountTypeType; typedef enum { balanceAbsolute=0, balanceCleared, balanceRunning } BalanceType; typedef enum { feeFixed=0, feePercent } AccountFeeType; typedef enum { ccAccountCategory=0, ccAccountPayee, ccCategory, ccCategoryPayee, ccIdPayee, ccMemo, ccPayee} CustomColumnType; typedef enum { rdOneRow=0, rdTwoRow} RowDisplayType; typedef struct { UInt8 recordVersion; UInt8 lastSyncFlag; // internal PocketMoney use double balance; // account balance -- sum of // all transactions within this // account. double cleared; // cleared balance -- sum of // all cleared transactions // within this account. AccountTypeType type; UInt8 reserved2; double limit; // credit limit or minimum // balance Boolean noLimit; // no limit is set UInt8 reserved3; double fee; AccountFeeType fixedPercent; Boolean totalWorth; BalanceType AbsoluteOrClearedBalance; CustomColumnType customColumn; double xrate; // exchange rate for this // account - (must be greater // than 0, it is usually 1.0) char currencySuffix[4]; char currencyPrefix[4]; Int16 currencyDecimalPlace; RGBColorType rowColor; // changed in version 2.5 of // PocketMoney is struct // {uint8,uint8,uint8,uint8} char *account; char *expires; char *number; char *phone; char *institution; char *notes; char *chknum; // next check number to use UInt32 lastSyncID; // last palm IR Synced to UInt32 lastSyncTime; // last time IR Synced RowDisplayType rowDisplay; } AccountDBRecordType, * AccountDBRecordPtr; -------------------------------------------------------------------------------- LookupDB Info -------------------------------------------------------------------------------- You'll need this info if you want to modify the popup lists in PocketMoney. // Lookup Types #define kAccountsListStr 1 #define kCategoriesListStr 2 #define kIdsListStr 3 #define kAmountsListStr 4 #define kClassListStr 5 #define kCurrencyPrefixesListStr 6 #define kCurrencySuffixesListStr 7 #define kCategoryExpense 0 #define kCategoryIncome 1 typedef struct { char *type; // this can be an above type or a // category name if the lookup name // is a payee char *LookupName; UInt8 incomeexpenseType; // only Category 4.0 and greater records have vvvvv the following fields char *notes; UInt8 budgetDayNotify; // 0 or 1 double budgetDayAmount; double budgetDayLimit; UInt8 budgetWeekNotify; // 0 or 1 double budgetWeekAmount; double budgetWeekLimit; UInt8 budgetMonthNotify; // 0 or 1 double budgetMonthAmount; double budgetMonthLimit; UInt8 budgetQuarterNotify; // 0 or 1 double budgetQuarterAmount; double budgetQuarterLimit; UInt8 budgetYearNotify; // 0 or 1 double budgetYearAmount; double budgetYearLimit; } LookupDBRecordType, * LookupDBRecordPtr; -------------------------------------------------------------------------------- FilterDB Info -------------------------------------------------------------------------------- Users create filters to show custom queries. #define kDateRangeNone 0 #define kDateRangeToday 1 #define kDateRangeYesterday 2 #define kDateRangeCurrentWeek 3 #define kDateRangeLastWeek 4 #define kDateRangeCurrrentMonth 5 #define kDateRangeLastMonth 6 #define kDateRangeCurrentQuarter 7 #define kDateRangeLastQuarter 8 #define kDateRangeCurrentYear 9 #define kDateRangeLastYear 10 #define kDateRangeOther 11 typedef struct { UInt8 recordVersion; Boolean currentAccount; // filter applies to currently selected account UInt32 dateFrom; // first day of month=1, today=2, date UInt32 dateTo; // first day of month=1, today=2, date TransactionType transactionType; // withdrawal, deposit, transfer, all FilterClearedType cleared; // cleared, not cleared, all Boolean recent; // show changed records since last sync char *filterName; char *account; // blank is all char *category; // blank is all char *payee; // blank is all char *chkNum; // blank is all char *className; // blank is all } FilterDBRecordType, * FilterDBRecordPtr; -------------------------------------------------------------------------------- RepeatingDB Info -------------------------------------------------------------------------------- Repeating record format. Has repeating info and a copy of the repeating record. // The following enum is used to specify the frequency of // repeating appointments. // enum repeatTypes { repeatNone=0, repeatDaily=1, repeatWeekly=2, repeatMonthlyByDay=3, repeatMonthlyByDate=4, repeatYearly=5 }; typedef enum repeatTypes RepeatType; // This structure contains information about repeat appointments. The // repeatOn member is only used by weelky and monthly-by-day repeating // appointments. For weekly the byte is a bit field that contains the // days of the week the appointments occurs on (bit: 0-sun, 1-mon, // 2-tue, etc.). For monthly-by-day the byte contains the day the // appointments occurs, (ex: the 3rd friday), the byte is of type // DayOfMonthType. This is based on Palm Calendar app repeating appts. // typedef struct { RepeatType repeatType; // daily, weekly, monthlyByDay, etc. UInt8 reserved1; DateType repeatEndDate; // minus one if forever UInt8 repeatFrequency; // i.e. every 2 days if repeatType daily UInt8 repeatOn; // monthlyByDay and EditRecurringWeekPushButton only UInt8 repeatStartOfWeek; // EditRecurringWeekPushButton only UInt8 reserved2; } RepeatInfoType; typedef struct { RepeatInfoType repeat; UInt8 recordVersion; UInt8 reserved1; UInt32 lastProcessedDate; TransactionDBRecordType transaction; } RecurringDBRecordType, * RecurringDBRecordPtr; =========================================================== ----- OLD FORMATS ------ =========================================================== I'm going to link the old versions of this file so you can see the changes. This file will only contain the latest database structures. Version 3.0 formats are available http://www.catamount.com/PalmApps/PocketMoneyPalmDBFormat30.txt -------------------------------------------------------------------------------- Contact Info -------------------------------------------------------------------------------- Catamount Software PO Box 8276 Essex VT 05451 http://www.catamount.com pocketmoney-palm@catamount.com Last modified : May 30, 2006 -------------------------------------------------------------------------------- Change History -------------------------------------------------------------------------------- May 30, 2006: PocketMoney 4.0: added support for budgeting to lookups April 15, 2003: PocketMoney 3.0: changed many things. October 2, 2001: PocketMoney 2.6 - type string changed in lookupDB records from the *_old characters to the numbers 1-7. Change required to deal with Japanese ROMs and multibyte characters. kPayeesListStr is undefined because it is the category name. Querying on the cateogry name will return all payees for that category. August 2001: PocketMoney 2.5 - added rowColor to the AccountDBRecord June 1999: Initial release