13 Replies Latest reply on May 9, 2015 1:13 AM by DavidChatterton

    Calculation

    DavidChatterton

      Title

      Calculation

      Post

      hi I'm adapting the starter solution invoices to better work for my company 

      i have added some fields call materials, labour, expenses and mileage for my purposes materials is a calculation field , the calculation being invoices:subtotal ( the same as it is in the starter solution) - (labour+expenses+mileage). Now this calculation works but when I input all the info into the fields the calculation shows a negative, then when I just click on the subtotal the correct number is then put into the materials field 

       

      what is happening here

       

       

        • 1. Re: Calculation
          philmodjunk

          There's a related table called invoice data that lists the individual items on the invoice. The invoice subtotal calculates a value that includes data from these related records. When you click the mouse on a blank area of the layout, you commit records and this is needed before this calculation field can properly update. If this is a summary field from InvoiceData or another related table, this will also be the case. You may be able to script an OnObjectSave trigger on one or more of these fields to commit records in order to more smoothly update calculations.

          • 2. Re: Calculation
            DavidChatterton

            The subtotal changes as soon as you enter a figure into the item column I just want my materials box to do the same

            • 3. Re: Calculation
              philmodjunk

              To answer in more detail requires knowing exactly how you have set up your calculation and whether this is the Invoices starter solution for FileMaker 13 or an older version as each version has different starter solutions.

              • 4. Re: Calculation
                DavidChatterton

                Thank you mate I have got that one sorted I have another issue though I have introduced a tick box to show/hide the amounts on the print layout and it works but only on the first line why is this

                • 5. Re: Calculation
                  philmodjunk

                  You'll need to describe what you have set up in much more detail.

                  What version of Filemaker are you using?

                  What do you mean by "tick mark" and what layout objects did you use to create it? Is this a button or a field? Is there a script involved or just a calculation.

                  And what method are you using to "hide" "amounts"?

                  What amounts are they? from what table?

                  Should "hiding" them affect a calculated result on the invoice or are you just making them not visible?

                  • 6. Re: Calculation
                    DavidChatterton

                    Im using filemaker 13 

                    ok here we go i would like to show/hide the "amount" field in table "invoice data" on the print layout.  I have created a field call "itemised" in "invoice data" table and set this field as a check box on the "invoices layout" so that when the box is checked a 1 appears in this field or it remains empty when it is unchecked this works i have proved that a 1 appears and then is removed when it is unchecked

                    then on the print layout i have entered "isEmpty ( Invoice Data::Itemised)" into the "hide object when" box in the inspector under behaviour under data tab.

                    This works fine but for some reason it only hides or shows the amount on the first line on the body of the print layout 

                    hope iv explained a bit better  

                    • 7. Re: Calculation
                      philmodjunk

                      I predict that the check box field that you added to the Invoice layout is not inside the portal row. Anytime you place a field from a related table on a layout without putting it inside a portal row, you see and modify data only for the first related record. You'd need to put this inside the portal row to InvoiceData and then you'll see that for your current setup, you'd have to then click this field for every records shown in the portal.

                      Looks like you need to define this field in the Invoice table instead of InvoiceData. That way, you can set the value once for the entire invoice and all invoiceData records can access the same field in the Invoice table.

                      • 8. Re: Calculation
                        DavidChatterton

                        Thank you PhilModJunk your are correct I have resolved that issue now I have another. Same Solution Invoices on the dashboard there is a portal that shows that years invoices but it only shows invoices from the first of may i need it to show invoices from the 5 of april and i have no idea where this calculation resides to change it to the date i require once again thank you for your help 

                        • 9. Re: Calculation
                          philmodjunk

                          Well once again, I have had to send my crystal ball out for repair--going to have get rid of it--never seems to work when I need it to. wink

                          I have now idea what relationship is set up for your portal.

                          I do not know what portal filter, if any is specified for it.

                          So I have no way to answer your question in this post.

                          Enter layout mode and double click the bottom portion of the portal (so as not to double click a field or button in the portal row) to open Portal Setup. What name do you see in "Show Related Records From"?

                          Is the check box for selecting a portal filter selected? If so, click Specify... and post the calculation in your next response.

                          Open Layout Setup... What name is selected in Show Records From?

                          Now open Manage | database | relationships and find the two boxes, one named in Show Related Records and one named in Show Records. How are they linked in a relationship? By what fields? With what operator (=, <, >, X, or ??? )

                          • 10. Re: Calculation
                            DavidChatterton

                            Sorry to hear about your crystal ball mate I guess I have over used it in my past few posts, If I ever meet you I'll buy you a new one.

                            as I'm using a filemaker solution I thought you would also have access to it my bad 

                            So the portal related records from is invoices

                            The calculation is

                            If ( IsEmpty ( Company Dashboard::Status Filter ) ; 1 ; Invoices::Status Order = Company Dashboard::Status Filter )
                            and 
                            Company Dashboard::Current Month = Invoices::Month 
                            and 
                            Company Dashboard::Current Year = Invoices::Year

                            It shows records from  "company dashboard"

                            ok the last bit I'm not sure what you mean by the two boxes but if you mean dashboard table and invoice table they are linked by "status filter" in dashboard table and "status order in invoices table and they are linked with X

                            Status filter in dashboard is set to Global

                            and this is the calc in status order

                            Let     ( 
                                days = Get ( CurrentDate ) - Due Date ; 

                            Case     (
                                    not IsEmpty ( Date Payment ) ; 2 ; 
                                    days ≤ 0 and IsEmpty ( Date Payment ) ; 1 ;
                                    days ≥ 0 ; 0 
                                    ) 

                                )

                            // 2 = Paid
                            // 1 = Unpaid
                            // 0 = Past Due

                            • 11. Re: Calculation
                              DavidChatterton

                              sorry i just read the above post back and i meant to put a laughat the end of the first sentence 

                              • 12. Re: Calculation
                                philmodjunk

                                as I'm using a filemaker solution I thought you would also have access to it

                                I do, but it won't have the changes to it that you've already made and I'm not about to make any assumptions about what changes you have or have not made at this point. I'm also making you go look at the design of your own file as a way to help teach you how to understand how it is designed.

                                The relationship is then

                                CompanyDashboard ---x--Invoices

                                This is a Cartesian join that matches any record in CompanyDashboard to all records in Invoices. The values of the two match fields do not affect what records are related in a Cartesian Join relationship. Without the portal filter, this portal would show all invoice records. The portal filter expression is what controls which records do or do not appear in the portal. So this is one of the calculations that you couldn't find. This expression has to evaluate as True (any number value except 0 or null is True in FileMaker calculations) for any given Invoice record before that record can appear in your portal.

                                Note how it is filtering for two things:

                                1) invoice records that have the same month and year as the current month and year.

                                2) invoice records that match to the current value of Status Filter--a global field that, without looking at the starter solution, I can tell is probably formatted with a value list with values of Paid, unpaid and past due. (the value list will actually enter the number values 2, 1, or 0 so that the portal filter can use that value to show either paid, unpaid or past due records in the portal.)

                                [note: then I went and looked. I was close. It was set up with some buttons that look like file tabs labeled with these values that set the status filter field to the numeric values for paid, unpaid and past due.]

                                Note that the system is not designed to allow you to specify the month, year, nor the day, it just works from the "current" values for month and year. In fact, it looks like you are expected to create a new dashboard record once a month to show you the invoices for each month, year unless there is a script that updates these fields, which is quite possible.

                                You could put the month and year fields on this layout and make them editable so that you can select a month and year, but what does the 5th of April represent? Is this something that you need to specify or do you need a different date range for your portal than what was originally designed in this starter solution?

                                • 13. Re: Calculation
                                  DavidChatterton

                                   

                                  PhilModJunk you are teaching me a lot and it is much appreciated laugh I am pretty confident finding my way round in the edit mode and adding fields and tables and some easy calculations but this is the area I need to work on calculations is there any books or other reference that would be useful?

                                  ok so the 5th of April is the beginning of our tax year 

                                  I understand what you have explained and I'm going to look into the creating a new dash board record but I have had a rethink and if the portal showed the last three months of invoices from the date shown in dashboard that would work well.

                                  So Im going to play about with that