1 2 Previous Next 15 Replies Latest reply on Sep 15, 2010 8:31 PM by RobertGardner

    Checkbox to calculate multiple values

    RobertGardner

      Title

      Checkbox to calculate multiple values

      Post

      I have a receipts layout from which I run a few reports that run sub-summaries off of the payment method.  

      On the actual layout, I have 3 input boxes: cash, check, and credit.  Also a calculation field formatted as a checkbox that marks whether the method is cash, check, or credit based on whether the corresponding fields are empty or not.

      Sometimes, however, I have customers that pay with two methods, and I would like to have the checkbox calculation result in two values, but I'm not sure how this can be accomplished.

      The reason for this is that on my daily accounting report, I have a sub-summary part that sorts by the payment method.  It currently only has to deal with one at a time and posts the total paid in the summary field, but I'd like to be able to have it split up the payments between payment methods if possible.

      Here's my calculation for the checkbox field:

      If (GetField (Cash) > 0; "Cash";

      If (GetField (Check) > 0; "Check";

      If (GetField (Credit) > 0; "Credit";

      If (Invoice Total ≤ 0; "Discount";

      If (Total Paid  ≥  0 and Owed < 0; "Acct Balance";

      If (GetField (Cash) > 0  and GetField (Check) > 0; ... "Cash Check" not sure what to do here??? ...

        • 1. Re: Checkbox to calculate multiple values
          philmodjunk

          Don't see why you need GetField for this, you can just refer directly to the the fields by name here, but the final line would be:

          "Cash¶Check"

          or

          List ( "Cash" ; "Check" )

          • 2. Re: Checkbox to calculate multiple values
            sunmoonstar.13

            Try this for you calc:

             

            Case (
            Cash > 0 and Cheque > 0 ; "Cash¶Cheque" ;
            Cash > 0 ; "Cash" ;
            Cheque > 0 ; "Cheque" ;
            Invoice Total ≤ 0 ; "Discount" ;
            Total Paid ≥  0 and Owed < 0 ; "Acct Balance" )

             

            The line in blue needs to go before the lines in red because Case tests are evaluated sequentially.

             

            Nick

             

            • 3. Re: Checkbox to calculate multiple values
              RobertGardner

              Alright.  That worked like a charm, but now I'm wondering if it's going to do what I had hoped.  I'd like to run a Daily Receipt report with a subsummary part that is sorted by this payment method.  Unfortunately, now that more than one value is possible in the Payment Method field (switched from a radio to checkbox set), it's not sorting at all.

              I can think of a way to do this with a Grand Summary part instead, but that prevents me from displaying the actual records and amounts below each different method of payment.

              Any ideas? 

              • 4. Re: Checkbox to calculate multiple values
                philmodjunk

                Hmmm, If someone is making a cheque and cash payment, that's two payments no? If you enter the two as separate entries, then your report will work and you don't need the above calculation in your check box field.

                Sometimes it works out better to have a related table of sub payments and each payment ledger record computes the sum of these sub payments. That enables you to have a portal listing each sub payment (one for check, one for cash, one for... etc.). If you use that approach, you can create your summary report on a layout based in this sub-payment table and you'll get your break downs by payment type.

                • 5. Re: Checkbox to calculate multiple values
                  RobertGardner

                  That's an intriguing idea, but I get the feeling that it will overly complicate the process.  With all of the tables that I'm weaving together, I hesitate adding another table that doubles or triples the amount of records I have to deal with to make the computations work.  My concerns are primarily cosmetic and ease of legibility, I suppose.  I'm sure I can make the Grand Summary work if I need it to.  It just isn't as easy to read.

                  • 6. Re: Checkbox to calculate multiple values
                    philmodjunk

                    Keep in mind I made two suggestions. Log two payments or add a sub payments table. Either way does what you appear to need here: a way to log the amount paid by cheque and the amount paid in cash. The "best fit" will depend on your business practices.

                    • 7. Re: Checkbox to calculate multiple values
                      philmodjunk

                      I'm also suggesting a single amount field for all payment amounts. Put the number in one field and use a payment method field to record what type of payement is being made. The result may be more records-- if you use a subpayment table--but much fewer fields in a simpler, easier to use structure.

                      • 8. Re: Checkbox to calculate multiple values
                        RobertGardner

                        Thanks for the responses.  I had already been logging two separate payments if the method was multiple, and I just wanted to streamline the process to reduce the amount of time between customers.  I didn't mean to just brush the suggestion off.  My concern with the sub payment table is that there will definitely need to be AT LEAST one record there for every receipt record, sometimes more (though admittedly not that often).  So I'm essentially creating two records for every transaction instead of one, which could get dicey over time and seems like an unnecessary compromise on file size.  I will, however, keep your suggestion in mind, as it may prove to be a more elegant solution to something else I am working one.  Thanks again.

                        • 9. Re: Checkbox to calculate multiple values
                          sunmoonstar.13

                          You know, from the outset I had wondered about the usefulness of having the calculation at all, but in the end, not knowing how your database was structured, I decided to simply answer the question at hand and offer the calculation. But in the ensuing discussion, it's become obvious that this is not the solution. Phil's suggestion of having a related table is correct.

                          Ok, so you have receipts and some of those receipts have more than one payment method. That's a "one-to-many" relationship and to correctly structure that into your database, you need two related tables (Receipts and Payments) related by a unique Receipt_ID number. The Payments table should be set up as a portal on your Receipts layout and with the Payments table set up to "Allow creation of records in this table via this relationship" (in the Edit Relationship dialog box). The calculation given above should be removed and the checkbox field should be changed back to a radio button field. The radio button field should be created in the Payments table, not the Receipts table. The radio button field is then added to the portal so that each Payment record in the portal can be individually categorised as "Cash", "Check",  "Credit", etc. There should only be ONE field (in the Payments table) for entering the dollar amount of each Payment, not separate "Cash", "Cheque" and "Credit" fields. Set up in this way, the portal will allow you to enter multiple Payment records related to a single Receipt record. The report will be based on the Payments table and should be set up to summarise by the radio button field.

                           

                          > I get the feeling that it will overly complicate the process

                          Having a separate table for Payments is not "overly complicating" things, it's standard practice for a situation like yours, even if having "multiple Payments for one Receipt" is something that only happens infrequently.

                           


                          > So I'm essentially creating two records for every transaction instead of one, which could get dicey over time and seems like an unnecessary compromise on file size.

                          Yes, every transaction would have one Receipt record and at least one related Payment record, but there's nothing "dicey" about that whatsoever. And your fears about file size are groundless. Having a separate Payments table will not blow out your file to an unreasonable size.

                           

                          >  I had already been logging two separate payments if the method was multiple

                          This is not good database design because creating two Receipt records to account for multiple payment methods will result in at least some of your data being unnecessarily duplicated, which is not a "streamlined" approach. Having a Receipts table and a related Payments table is the streamlined approach.

                           

                          Nick

                           

                           

                          • 10. Re: Checkbox to calculate multiple values
                            RobertGardner

                            Thanks for the explanation, Nick.  As you can tell, I'm not exactly trained in good database design.  I "inherited" the database that my company now uses (created in FMP 3 and never updated), and the method that we have in place is how things were originally designed.  I have been updating (and making some significant improvements to) the database based on how things were done in the past, and so a related Payments table was completely off the radar.  If I put one of those in place, how will a record import proceed from the old version (one payment and one payment type) to the new (two different tables)?  Am I importing the same records twice based on the invoice id#?

                            • 11. Re: Checkbox to calculate multiple values
                              sunmoonstar.13

                              > As you can tell, I'm not exactly trained in good database design.

                              Neither was I when I first stared using Filemaker!

                               

                              > I "inherited" the database that my company now uses (created in FMP 3 and never updated), and the method that we have in place is how things were originally designed. 

                              Ah, I see, a difficult situation, but not impossible to overcome.

                               

                              > I have been updating (and making some significant improvements to) the database based on how things were done in the past

                              Good to hear!

                               

                              > If I put one of those in place, how will a record import proceed from the old version (one payment and one payment type) to the new (two different tables)?  Am I importing the same records twice based on the invoice id#?

                              Ok, so every Receipt record has an Invoice ID#? In that case, it shouldn't be too difficult.

                              1. Make a backup of your database and test your backup to make sure that it's functioning normally. In fact, make two backups, just in case.

                              2. Then, from within your original Receipts database, go to File menu > Import Records > File. Select your Receipts database.

                              3. A dialog box called "Import Field Mapping" will pop up. Select the Receipts table under the "Source" menu. In the Target menu, select "New Table". It will probably say "Receipts 2". Select this, you can change the name of the new table later.

                              4. By clicking the little arrows between the fields, you can select which fields to map across to the new table. Make sure that you map the Invoice_ID, Amount and Payment_type fields into the new table.

                              5. Select "Add new records" under the "Import Action" section, then click the Import button. A new table called "Receipts 2" will be created. It will contain new records with the mapped fields.

                              6. Open Manage Database and change the name of the new table to Payments. Go the Relationships tab and rename the "Receipts 2" table occurence as well.

                              7. Use Invoice_ID as the key field to relate the Receipts table to the Payments table.

                              8. Double-click the linking icon between the two table occurences in the Relationships graph and select "Allow creation..." and "Delete related records..." under the Payments table. Then click OK to exit the Manage Database dialog.

                              9. Set up the portal in your Receipts layout, as I described above. You should then see the related Payment records in the portal.

                              10. If everything is working the way it's supposed to at this point, then you can safely delete the redundant Amount field and the Payment_type field from the Receipts table, because this data now exists in the Payments table.

                              11. Then delete all the redundant records in the Receipts table, that is, the "extra" Receipt records that were originally created to account for multiple payments. Of course, don't delete the initial record of each group of multiple Receipt records, only delete the "extra" records.

                              12. If things went seriously belly up somewhere along the line, you can always delete the file, create a copy from one of your backups, and start over.

                              Good luck!

                               

                               


                              Nick


                               

                              • 12. Re: Checkbox to calculate multiple values
                                RobertGardner

                                Wow! Thanks, Nick.  I didn't realize I could create a new table directly from the file import.  Yes, fortunately there is an invoice id# for all of the existing records.  (Even if there weren't, that wouldn't be too difficult to overcome).  This looks like the solution I was looking for. 

                                • 13. Re: Checkbox to calculate multiple values
                                  RobertGardner

                                  Okay, one other question that is a bit unrelated...

                                  I'm trying to run a script that locks a record to prevent editing after printing it.  The script simply sets a lock field value to 1.  That is what I've read is the best way to do it.  Then that field can serve as an access privilege grantor or something of that sort.  

                                  Unfortunately, I cannot figure out how to set access privileges based on that field.  Am I missing something?

                                  • 14. Re: Checkbox to calculate multiple values
                                    philmodjunk

                                    In FileMaker Help, look up "Editing record access privileges" and scroll down to the section labeled: "Entering a formula for limiting access on a record-by-record basis".

                                    1 2 Previous Next