9 Replies Latest reply on Jun 28, 2013 9:02 AM by janslort

    Drop Down List of Blank fields from a List view.

    janslort

      Title

      Drop Down List of Blank fields from a List view.

      Post

           I'm using FM11Advanced on a PC running Windows 7.

           I have a list of invoices which I have sorted by customer name and invoice numbers.  I want to use it as a list to apply payments from the customers.  There is a field for Amount, and a field for Date.  I must allow for a possibility that a customer will make more than one partial payment for an anvoice.  Rather than show each of (4) possible fields as Pay1, Pay2, Etc., either vertically, or horizontally, it would be much more efficient if a drop-down would appear from "payments" with the payment amount fields and Payment Date fields from "Dates" when a payment is only a partial one.  I can setup the fields as a (4) repeating field, or as Pay1, Pay2, ect. with a (Summary) of all four being deducted from the invoice balance.

           Any Ideas anybody?

        • 1. Re: Drop Down List of Blank fields from a List view.
          philmodjunk

               Not only could you have partial payments, couldn't you also have payments that pay off more than one invoice? and payments that accidentally over pay what is owed? The design needed to manage payments against invoices that are not paid in full at the moment the transaction is finalized is often more complex than a database developer originally imagines.

               Instead of multiple fields, think multiple related payment records that can be linked to one or more invoices:

               Invoices----<Invoice_Payment>------Payments

               Invoice::__pkInvoiceID = Invoice_Payment::_fkInvoiceID
               Payments::__pkPaymentID = Invoice_Payment::_fkPaymentID

               There are several variations possible for how to implement this structure in a FileMaker layout, but the basic method is to create a new record for each payment received and then--either automatically via script or by hand, the payment is distributed over one or more unpaid invoices for a selected customer. A number field in Invoice_Payment records the portion of the Payment Amount that is applied to a specific invoice.

          • 2. Re: Drop Down List of Blank fields from a List view.
            janslort

                 Dear Phil::

                 I know about the variations in customer payments and have handling within the program.  I want to have a drop down list of 4 blank, or  a  partially filled list of payments  a clerical can enter an amount into.  (same with a corresponding ffield for dates)  I just can't figure  out how.  A Value list doesn't work because i dont wwant  preset values,, even with the EDIT  option it's not working here.  I'm stuck. 

                  

                 Jan Slort

            • 3. Re: Drop Down List of Blank fields from a List view.
              philmodjunk

                   What you ask for contradicts it self. You ask for a drop down list but then say "A value list doesn't work, because...". A drop down list IS a value list.

                   Can you describe in more detail what you mean by a "Drop down list of blank or partially filled..."? Can you post an example with actual numbers to explain how you want that to work.

              • 4. Re: Drop Down List of Blank fields from a List view.
                janslort

                     Phil:

                     Sorry to be so unclear. What I mean is a drop down list presents a list of predefined  values. I. E. $100.00,$ 200.00 $300.00 and an Edit etc. that's not what I,want. I would like to be presented with a drop down list of 3 or 4 empty fields that are designated as payment1, payment 2, payment3 and so on.  That I can fill in with data, same when I click on the payment date field on the list, I get 3 emty date fields I can fill in.  If I show these fields on the list either as separate lines,or horizontally on the same line, I will have either a list that is too wide, or has 4 lines per record (invoice) that's awkward for hundreds of invoices. 

                     Jan

                      

                • 5. Re: Drop Down List of Blank fields from a List view.
                  philmodjunk

                       Yes, but drop downs are not limited to "predefined values"--they can be produced dynamically from the data currently entered in the database and a drop down list lists VALUES, not fields for data entry. A Portal can present the user with a flexible number of rows for entering data. You can even set up a button in combination with a tab control such that clicking the button populates the portal with new related records (with or without date entered automatically) and then changes tab panels to reveal that portal to the user.

                       The first part for making this work, however, is the right data model. That's why my first comment posted here described a data model for handling all possible ways a payment might need to be handled for paying off anything for part of one invoice to multiple invoices. That may not be the right model for you, but any further discussion here has to start with some kind of data model so that we can look at how those portals might be set up to work for you.

                  • 6. Re: Drop Down List of Blank fields from a List view.
                    janslort

                         Phil:

                         I follow most of what your are saying except the portal part.  Can I have a portal showing "Related records": from the same (Invoices)  file  What I jhave now is a list of invoices since its sorted by customer name and Invoice number, its easy to spot where a customers payment would need to be applied.  I have a field called "Payment1" in which the payment gets entered.  If one check is for several invoices, the amounts get apportioned over as many invoices as the payment covers.  If its short, it leaves a blance due, if its too much, a credit balance results.  Both are easy to spot and and handle.  When I sort by date a Sub-total gets computed, I get a clearly identified figure for the each day's receipts..  These amount them gets posted to the bank account.as deposits.  Printed for a month, I have a concise list to compare to the bank statement for reconcilliation.

                         When I place three more fields for Payment2, 3, & 4, together with their dates2, 3 & 4 and check numbers 2, 3, & 4, I have a string of fields that is too wide for the screen.

                         So can I create a portal in chich these 4 payment fields with their corresponding dates and check numbers will be shown? Or a small window with a layout for these fields whenever an invoice will have more than one payment (button or script trigger activated)?

                         Then last of theseissues, can I preserve the ability to sort all the payment dates (1, 2, 3, & 4) into a single list to be sorted by date to produce me "receipts for the day" reports.  I tried copy & paste from the various payment date1 2 3 & 4 into an aAll Date field, I even made it a 4 repeating field.  The individual dates and amounts post O.K. but when I sort, only the oder within each record sort, instead of a comprehensive list.

                          

                         Jan

                    • 7. Re: Drop Down List of Blank fields from a List view.
                      philmodjunk

                           Please read my very first post again. Instead of using multiple fields for recording the payments. Use multiple records in a related table for recording the payments. From what (I assume) is a list view of your invoices, you can add a button that selects that invoice record and changes layouts to a layout for recording a payment made against that invoice. This button's scritp can even pop up a small floating window for recording that information and then you can enter data and dismiss it to return to your layout in order to process the next payment. But you need to also determine whether or not it is ever possible that you might receive a single payment that covers multiple invoices or not. It depends on business rules as to whether or not that is ever possible. If a payment can cover multiple invoices, you'll need a separate table for the payments received as well as a join table for distributing that payment over multiple invoices.

                           Either way, you can still get summary reports of your data that will include payment totals and sub totals.

                      • 8. Re: Drop Down List of Blank fields from a List view.
                        janslort

                             Phil: 

                             Thank you.  I followed your solution except for the "floating window" part.  When I created the new window, I sized it and positioned it, but I was unable to eliminate the "status" tool bar, which made it kind of big and ugly.  Also when I executed the script, all (9) of my windows moved their frames on my screen and made a poor visual impact for my user.  I just wrote a "go to layout for related record" script and triggered it by a Red Arrow so a payment could  be entered on the Payment For.  The totalwill show in the "Paid Amounts" column for that particular invoice. This "List" approach is fast and easy for the data entry person.

                             BTW If a client sends say a check for a round amount (10,000.00) "On Account" which is more than any one invoice, I record the full payment for as many invoices as I need till the amount is used up.  Usually that leaves a balance of the last invoice, or a Credit Balance.  If a customer designates a payment for a particular invoice, it is required by accounting rules that the payment be applied to that invoice, even if it is > or < the designated invoice.  Credit balances are easily applied to future invoices as they are issued, or refunded if that is appropriate.

                             I have made screen shots of the "List" and the "Payment" screen. 

                        • 9. Re: Drop Down List of Blank fields from a List view.
                          janslort
                          /files/2435e01b6e/Invoice_List.gif 1280x800