1 2 Previous Next 15 Replies Latest reply on Jun 13, 2014 1:54 AM by donkelly

    Access to FM newbie

    donkelly

      Title

      Access to FM newbie

      Post

           hi all.

           Am converting myself to Mac and FM after many years on 'the other side'. A couple of questions though.

           I'm designing an accounting ledger file in FM13 attempting to replicate what i have in Access. 

           1. the ledger uses various accounts to spend and receive money to and from. How would i go about calculating an account balance for each account. I can get a balance across all accounts but not each account. The set up is that each account has an opening balance and as transactions are entered i need to see a closing balance.

           2. Transactions: In access i can specify a transaction type using a category field: Field Name; Income or Expense. i can then manipulate the actual amount of the transaction depending on the expense type. EG:  salary amount would be categorised as income and the amount entered, say 1000 would remain as a positive number. On the other hand a Rent amount would be categorised as an expense and the amount entered would become negative amount -350. This method uses the Fieldname.column lookup in a query. Can i replicate this in FM?

           Many thanks in anticipation.

            

        • 1. Re: Access to FM newbie
          philmodjunk

               For ledgers, I prefer to have a debit field, a credit field, and a calculation field, cBal defined as Debit - Credit. There are alternative methods that use a single amount field with positive and negative values in a single field. If you use that method, refer to that field in place of cBal in the following answers:

               1)

               Option 1: Define a table of accounts, one record for each account that you link by account ID to your transactions in the transactions table. A calculation field defined as Sum ( Transactions::cBal ) will then return the current balance for that account.

               Option 2: Define a summary field in Transactions as the running total of cBal. Select the "restart summary for each sorted group" option and select your Account ID field as the "group" field. In a list or table view layout, if you sort your transaction records by Account ID, you'll see a running total for your current balance that restarts with each account in the found set. (And your found set can be records for one account or multiple accounts).

               Option 1 and 2 are not mutually exclusive.

               2) I'm not sure that I understand the question. Are you validating the amount entered to ensure that it is consistent with the type of transaction or are you looking up "standard amounts" for specific transaction types? Both are possible.

               See this thread for a more detailed look at setting up a "bookkeeping ledger" in FileMaker: https://dl.dropboxusercontent.com/u/78737945/AccountingLedger.fmp12

          • 2. Re: Access to FM newbie
            donkelly

                 Thanks for that file Phil. Looks like it will be most helpful.

                 The idea behind the Category type is I want to have only one data entry form to enter transactions. 

                 So I will have: TransactionID, Date, To/From Account, Category, SubCategory, Amount, notes etc etc, ActualAmount.

                 The amount entered will always be positive but I want the Actual Amount to invert to negative if the Category Type is an expense.

                 This is how the IF expression looks in Access:  ActualAmount: Iif([CategoryType]="Expense",[Amount]*-1,[Amount]) .

                  

                 Thanks again

                  

            • 3. Re: Access to FM newbie
              philmodjunk

                   The same expression and syntax works in FIleMaker as well, just remove the [] and replace commas with semi-colons. You can define ActualAmount as a calculation field with a number result type. In Access this column would be defined as part of the data source SQL query, in FileMaker, you define it as an actual field in Manage | Database | Fields.

              • 4. Re: Access to FM newbie
                donkelly

                     thanks again Phil but how can i get FM to lookup the Type column though? 

                     Sample Data from Categories table.

                     CategoryID                   CategoryName                 CategoryType                 Notes

                      1                                  Wages                              Income

                     2                                    Bills                                  Expense

                     So if i'm entering a transaction and i select Wages category,  FM needs to know that this is an income cat so do nothing as far as the amount entered is concerned. how do i achieve this?

                      

                      

                • 5. Re: Access to FM newbie
                  philmodjunk

                       If you have a table of category types and names. If you link this table by CategoryName or CategoryID to a field in your ledger, there are auto-enter field options that can copy over the categoryType when you select the Category from a value list. If you link by CategoryName, format the CategoryName field in your ledger with this value list, but you can also use a CategoryID field for both relationship and value list such that you select a category by name from the value list, but the value list enters the ID--producing the same results you might get from an Access combo box.

                  • 6. Re: Access to FM newbie
                    donkelly

                         Thanks Phil. I got the CategoryType to work the way I want but I just can't get the If statement to read the field correctly.

                         i have: If (CategoryType ="Expense" ;Amount *-1 ; Amount) but this always returns a positive amount for some reason.

                         thanks again.

                    • 7. Re: Access to FM newbie
                      philmodjunk

                           What data type is the CategoryType field. Check this in Manage | Database | Fields. I'm guessing that CategoryType is a number field instead of text. And if you use a value list to select categories in this table, make sure that "Expense" is the actual value entered into the field for Expense entries. If you have a value list that enters a number code for each category while displaying the names, then your if step needs to check for the number values instead of the quoted text string: "Expense".

                      • 8. Re: Access to FM newbie
                        donkelly

                             Thanks Phil. I've actually removed the numbered 'ID' field from my tables (kept it in transactions though) to resolve any lookup problems i might experience.

                             In the transactions table, i have the Category as a lookup to the Category table: 

                             on the transactions layout I have the Category using a value list:

                             (i'm using both columns because some categories can be both an expense and an income type. EG: cash)

                             and then somehow i have a text box retrieving the correct categorytype:

                             it works but i don't know how!

                             but i can't get the IF function to work looking at the categorytype field.

                              

                        • 9. Re: Access to FM newbie
                          philmodjunk

                               I don't fully follow your description. "I'm using both columns"???

                               And "text boxes" do not exist in FileMaker. They are layout field objects and each refers to a field defined in a table unlike a VB type "form" or "report" in Access where you can create such a text box that is not bound to any database field.

                               And in FileMaker, a number field can store non-numeric text, but if you do so, it won't evaluate correctly in a calculation.

                               Thus, as I have already suggested, you need to check what data type was specified for the field you are using in your calculation. This is something that you would check in Manage | Database | Fields.

                               And since you describe using "both columns", I have to wonder if the field you have formatted with a value list is the same field you are referencing in this calculation.

                          • 10. Re: Access to FM newbie
                            donkelly

                                 Right, got it sorted and working the way i want it Phil. Thanks for the advice.

                                 now for the account balance summary...! Will let you know how i go.

                            • 11. Re: Access to FM newbie
                              donkelly

                                   Hey Phil, got the balance thing sorted. I was concentrating on the running balance when i didn't really need to. Not yet anyway...

                                   Anyhoo, I have a new problem now. i have a table of Bills related to Transactions by BillID. What I want to do is:

                                   From my Bills layout I want to open a Bill payment (Transaction) layout and copy the BillID, Payee and amount onto this second layout.

                                   How could I go about this?

                                   TIA, Don.

                              • 12. Re: Access to FM newbie
                                philmodjunk

                                     So from your Bills layout based on the Bills table, you want to create a new record in Transactions linked by BillID to the current record on your Bills layout.

                                     Set Variable [$BillID ; Bills::__pkBIllID ]
                                     Go to layout [ "Transactions" ( Transactions ) ]
                                     New Record/Request
                                     Set Field [Transactions::_fkBillID ; $BillID ]

                                     But use your field and table occurrence names in place of mine.

                                • 13. Re: Access to FM newbie
                                  donkelly

                                       Thanks again Phil. Problem though:

                                       The new transaction record shows the data from the Bills layout (as per Script) but this data won't save to the transactions table.

                                       I add the date and the account the payment is coming from and these are saved but the 'copied' data isn't. I added a Commit button to try and work around this but it doesn't work.

                                        

                                       thoughts?

                                       D.

                                  • 14. Re: Access to FM newbie
                                    philmodjunk

                                         I don't know what you mean by "this data won't save to the transactions table". Can you describe what you mean in much more detail? This data is most definitely "saved" or you wouldn't be able to see it on the screen, so something is creating the illusion that it is not.

                                         

                                              I add the date and the account the payment is coming from and these are saved but the 'copied' data isn't.

                                         Into what are you adding that data? What field, from what table and on what layout?

                                         Best guess is that the data set in fields by the script is in one table and the fields where you are adding this additional data are from a different table, but that's just a guess at this point...

                                    1 2 Previous Next