10 Replies Latest reply on Dec 19, 2009 9:29 AM by comment_1

    Create new record in related file on field change

    jgm_1

      Title

      Create new record in related file on field change

      Post

      System: FM Pro 9 Advanced shared on OSX & Windows.

       

      I have a database for a costume hire business. Customers are able to take a costume immediately, or leave a deposit & pay the remainder later when they collect the costume.

       

      I need to be able to generate an accurate printout of daily takings on a regular basis for our auditor.

       

      I would like to be able to automatically generate a new record in an Activity Log whenever an invoice is created or has one of a number of trigger fields altered. I've created an Activity Log and related it to Invoices, but can't get the relevant invoice data (Deposit, Hire Amount) into Activity Log fields. 

        • 1. Re: Create new record in related file on field change
          ninja
            

          Howdy jgm,

           

          Could you explain more about your current design?

           

          I picture a "Costumes" table related to an "Activity" table by CostumeID.

           

          The Activity table would have fields such as Name, Add, Phone#, Rental Amount, Downpymt amount & Timestamp of that payment, balance payment amount and timestamp of that balance payment. 

           

          Your daily activity would simply be a subsummary report of the found set of:

          DwnpymtAmtTimestamp = date of interest OR BalancePymtTimestamp = date of interest

           

          The new record you speak of would not be necessary since you're timestamping all money transactions on the existing record.

           

          A Downpayment button would run a script of:

          SetField [DwnpymtAmtTimestamp ; Get (CurrentTimeStamp) ]

          GoToField [ DwnpymtAmt ]

           

          and similar for the balance payment.

          • 2. Re: Create new record in related file on field change
            jgm_1
              

            It's not quite that simple. There are 5 main files - Invoices, Line Items, Customers, Costumes and Themes. Invoices has a portal to Line Items which relates to Costumes. A costume may comprise a number of items, & these are not necessarily related (eg a costume for a movie star theme may have a shirt from one part of the collection, a suit from another part, a tie from somewhere else, shoes, belt etc) hence the need for the portal. All financial details are in Invoices. Customers are charged a bond when they hire a costume which is refunded when they return it. There are dates on the creation of an invoice, the event date the costume is required, the date the costume is to be returned by, the date a costume will be available for collection, the date of a final payment, and the date the last item is returned. The portal has dates for each item collected and returned.

             

            My problem is that I can produce separate lists for daily takings when a costume is taken immediately (ie the invoice date), and a separate list for costumes which have been collected and paid for after the invoice date, but I need some way of consolidating them. 

             

            I need to have daily takings sum all payments made on the invoice date plus those for costumes collected on that date, but the only way I can distinguish the latter is by the final payment date. I have been able to dump both lists into excel spreadsheets and consolidate them, but it several hours work each time.

             

            I figured the easiest way around the problem would be to set up another table with a new record generated automatically whenever a new invoice record is created, or when certain fields are altered. I'm not wedded to this approach if there is another way around it though.

            • 3. Re: Create new record in related file on field change
              philmodjunk
                

              You could upgrade to FMP 10. Version 10 has script triggers and you can use this feature to cause a script to be triggered when the field is altered.

               

              A restructure of your database might also be in order.

               

              The way I read it, an invoice can list several costumes which may all be picked up and returned on separate dates but the line items list the different items that comprise a "costume" so you don't have one line item record for each costume listed on the invoice.

               

              If I am interpreting your description correctly, you might want to create special "costume" line item records so that they read like this in your portal:

               

              Item #  Desc         Picked Up       Returned       Costume

              123      Cowboy     12/1/2009      12/15/2009        x

              456      Shirt         

              321      Pants

              234      Hat

              987      Ballerina     12/20/2009                           x

              and so forth.

               

              Performing a find for all line item entries with "x" in the Costume field will filter out the components and just give you a list of costumes with their in and out dates. In the above example, the Ballerina costume as not yet been returned.

                             

              • 4. Re: Create new record in related file on field change
                jgm_1
                  
                PhilModJunk wrote:

                You could upgrade to FMP 10. Version 10 has script triggers and you can use this feature to cause a script to be triggered when the field is altered.

                 

                If I am interpreting your description correctly, you might want to create special "costume" line item records so that they read like this in your portal:

                 

                Item #  Desc         Picked Up       Returned       Costume

                123      Cowboy     12/1/2009      12/15/2009        x

                456      Shirt         

                321      Pants

                234      Hat

                987      Ballerina     12/20/2009                           x

                and so forth.

                 

                 

                An upgrade is out of the question at the moment. The business is "not for profit", and software upgrades for our 3 computers is a fair way down on the list of priorities. We do charge a hire fee to cover costume cleaning, repairs, updates, premises rental etc, but there is not a lot of money in it.
                 
                I started work on the database 8 or 9 years ago when we were associated with a local amateur theatre company. It was an example small business (stock control?) application for FM 4 or 5 I think which was available at that time on the Filemaker web site. The ladies initially wanted a database to keep track of their costumes & to help locate them in an old house we were using at the time. The example database seemed to cover most of what they wanted as well as having scope for further applications. In those days, an amateur group would source costumes from a number of places for each new show, & returning them to the right place after the production was a major hassle. This system enabled us to sew a numbered label onto each item. This also helped when we loaned costumes to other groups, as both groups now only had to tick off a numbered list to ensure all costumes were returned. Just over a year ago, we went out on our own so that we were able to service a much wider range of customers, although we are still entirely staffed by volunteers. We also hire to the general public to help offset costs for community groups. Over the years the database has been fairly extensively modified, and a new set of tables added to assist customers looking for costumes for themed events.
                 
                My portal records do look like your example, and I have scripts to check items outstanding & overdue. My problem is that I need to produce a record of daily takings for our auditor, without having to provide him with several hundred separate sheets of paper. I do have a script which we use for our daily reconciliation which produces a summary total for the day's hirings and accounts for deposits and pick ups. The database also has built in reporting functions which use summary fields. I have tried to use a modified report, but I can only either find all invoice record to generate daily totals, or find all invoices with a final payment date (this is only entered if a costume has been ordered earlier). For the auditor, I need an array which effectively hold the output of my daily takings script, with a date-stamp. My thought about using another table with new records generated automatically, is that it would provide a full audit trail which may be useful to assist in training our volunteers. 
                 
                I did try to create an activity log, but can't get the details linked in. I suspect I would have to use a portal do this sort of thing, but it seems to work opposite to what I want to do. I want a new activity log record generated from changes in an Invoice, but I don't want to have another portal in the invoice. Ideally the portal should be in the activity log. 


                • 5. Re: Create new record in related file on field change
                  comment_1
                    

                  jgm wrote:
                  My problem is that I need to produce a record of daily takings for our auditor, without having to provide him with several hundred separate sheets of paper.
                  ...
                  For the auditor, I need an array which effectively hold the output of my daily takings script, with a date-stamp. 

                  Can you provide an example of what such report should look like? It seems you already have all the required data in the LineItems table.

                  • 6. Re: Create new record in related file on field change
                    philmodjunk
                      

                    You should be able to create the report based on the LineItems Table Occurrence you are referencing in your portal.

                     

                    You can perform finds to filter out the component entries, leaving just the costume entries. You can further refine your find to find just those records that fall in a given date range or where a given field is empty (Costumes picked up but not returned for example, would have an empty return date field.)

                     

                    This should give you the report you need.

                    • 7. Re: Create new record in related file on field change
                      jgm_1
                        

                      The report is really simple:

                       

                      <!--    StartFragment    -->  Date Income       Saturday 12-Dec-09 $350.00 Friday 11-Dec-09 $405.00 Thursday 10-Dec-09 $210.00<!--    EndFragment    -->

                       

                      The LineItems table doesn't contain any financial data, that is all in Invoices.

                       

                      The relevant parts of Invoices are:

                       

                      Hired Before Today Calculation Unstored, = If (Collected Date = Get ( CurrentDate )  ;  Payment Amount - Deposit ;0)

                       

                      Hired Today Calculation Unstored, = If ( Invoice Date = Get ( CurrentDate ) ; If(Payment Amount   ≠ 0  ; Payment Amount ; Deposit) ; 0) - If(Refund Date =  Get ( CurrentDate );Refund;0) 

                       

                      Daily Takings Calculation Unstored, = Hired Before Today + Hired Today - (If(Refund Date = Get(CurrentDate); Refund;0)) 

                       

                      Daily Total Summary = Total of Daily Takings 

                       

                      Hired Before Date  Calculation Unstored, = If (Collected Date = Search Date  ;  Payment Amount - Deposit ;0)

                       

                      Hired On Date Calculation Unstored, = If ( Invoice Date = Search Date ; If(Payment Amount   ≠ 0  ; Payment Amount ; Deposit) ; 0) - If(Refund Date =  Get ( CurrentDate );Refund;0) 

                       

                      Search Date Date

                       

                      Search Period Text Global

                       

                      Daily Takings Report Calculation Unstored, = Hired On Date + Hired Before Date

                       

                      Daily Total Report Summary Total of Daily Takings Report

                       

                      I have tried to get the result with the following script: 

                       

                       If [ not IsEmpty ( Line Items::Search Period )]
                       End If 
                       Perform Script ["Find All Records"]
                       Sort Records [Restore; No dialog]
                       Go to Layout ["Summary by Day"(Invoices)] 
                       Enter Preview Mode [Pause] 

                       

                       

                      The Summary by Day layout has a sub-summary part containing Invoice Date and Daily Total Report fields, but it doesn't produce results for Hired Before Date calculation.



                      • 8. Re: Create new record in related file on field change
                        comment_1
                           These calculations don't make sense (to me): being unstored, they return different results, depending on the current date. On any given day, you should be able to produce an accurate report for any chosen period.

                        Unfortunately, you left out the part about how (and when!) are the fields PaymentAmount, Deposit and Refund populated. It seems to me you should add a receipts section to your invoice, something like:

                        DateDue      Type      Amount    DateReceived
                        12-Dec-09    Deposit    100.-    12-Dec-09
                        20-Dec-09    Payment    300.-    23-Dec-09
                                     Refund     -55.-    28-Dec-09

                        This would be a portal to a CashTransactions table - and from this table you could easily report your projected or actual cash flow on any day.


                        • 9. Re: Create new record in related file on field change
                          jgm_1
                            

                          comment wrote:
                          These calculations don't make sense (to me): being unstored, they return different results, depending on the current date. On any given day, you should be able to produce an accurate report for any chosen period.

                          Unfortunately, you left out the part about how (and when!) are the fields PaymentAmount, Deposit and Refund populated. It seems to me you should add a receipts section to your invoice, something like:

                          DateDue      Type      Amount    DateReceived
                          12-Dec-09    Deposit    100.-    12-Dec-09
                          20-Dec-09    Payment    300.-    23-Dec-09
                                       Refund     -55.-    28-Dec-09

                          This would be a portal to a CashTransactions table - and from this table you could easily report your projected or actual cash flow on any day.


                          If a costume is taken immediately PaymentAmount is entered when the invoice is created, otherwise the Deposit is entered. In the latter case, the PaymentAmount is entered when the costume is collected. The payment amount is always the full amount for the hire, and if a deposit has been left, the amount a customer pays on collection is PaymentAmount - Deposit. The Refunds field is used where a refund has been given (eg for a costume taken but not used) or with a negative amount where part or all of the bond has been retained.
                          I don't know how to create the CashTransactions table. How would the entries relate to my current Invoices table entries? Also, how would entries be made in it? Would the operators have to navigate to a separate layout?
                          The example database had a portal in Invoices to Line Items which looks up items in the catalogue. To create Themes, I copied Invoices and LineItems, changed the names and descriptions to Themes and ThemeItems & added relationships between ThemeItems and Catalogue & Themes and ThemeItems. I have never created a portal from scratch.
                           

                           



                          • 10. Re: Create new record in related file on field change
                            comment_1
                              

                            jgm wrote:
                            How would the entries relate to my current Invoices table entries? Also, how would entries be made in it? Would the operators have to navigate to a separate layout?

                            It's a simple one-to-many relationship: since your invoice can be paid in several installments, you would record each payment as a record in a related table, instead of using multiple fields in the invoice itself. This way you can easily produce a report of payments grouped by date (which you couldn't do from the Invoices table, because the same invoice cannot appear in two groups at the same time).

                             

                             

                            Practically, you'd create a new table with fields for:

                            PaymentID (auto-entered serial number)

                            InvoiceID (this is the matchfield to the parent invoice)

                            + the fields mentioned above

                             

                            Define a relationship to Invoices, matching on InvoiceID. Allow creation of records in the Payments table via this relationship.

                            Finally, place a portal to Payments on the invoice layout.