8 Replies Latest reply on Aug 12, 2015 5:43 AM by WilliamMartin_1

    Conditional Calculations?

    WilliamMartin_1

      Title

      Conditional Calculations?

      Post

      Hey everyone,

      Ok, so on our invoices (I've attached a screenshot of the pertinent area) we enter the Number of items into the "No." field, Select the type of garment or paper from a dropdown list in the "Garment / Paper" field, and at the bottom left a "total pieces" number is calculated by adding together the numbers in the "No." fields.

      What I'm trying to do is make it so that when a certain line item (one that's an addition to the cost, but not an addition to the total number pieces) is selected from the dropdown list in the "Garment / Paper" field, it's value in the "No." field is ignored by the "Total Pieces" calculation.

      I'm not sure if that is clear, so let me try to give an example of my intended result: Let's say if "American Apparel Tr308 Tank" (from the first line item) was instead - I don't know - "Bloop", the "Total Pieces" calculation would ignore the "No." value for that line, in this case the 54. Does that make sense?

      I'm relatively new to filemaker, and this is totally stumping me. I don't feel like I did a great job explaining, so please let me know if there's any information I can give to help.

       

      Thank you guys!

      Screen_Shot_2015-08-11_at_11.51.55_AM.png

        • 1. Re: Conditional Calculations?
          philmodjunk

          Is this a portal that you show in the screen shot or repeating fields? Hopefully it's a portal as repeating fields are not the optimum method to use here.

          If you select "bloop" to quote your example, why do you need any value in the NO field? one very simple way to handle this is to just leave the quantity column empty for such "non-inventory" items since you apparently are not going to count them anyway.

          Other options are also possible that will allow you to specify  a quantity in the No. field, but they will all start from using one method or another to identify which items are not to be counted as part of this total. A calculation field, for example could be defined in the portal's table with an if function that returns the value of the No. field only if it is am item to be counted and then you set up your total pieces field to sum the value of this calculation field instead of the No. field.

          but that If function will require some form of data in the portal records that identifies which records are which.

          • 2. Re: Conditional Calculations?
            SteveMartino

            What I'm trying to do is make it so that when a certain line item (one that's an addition to the cost, but not an addition to the total number pieces) is selected from the dropdown list in the "Garment / Paper" field, it's value in the "No." field is ignored by the "Total Pieces" calculation.

            How do you know when the 'certain line item' needs the No. field to not be counted?  Does that certain line item always need tonot be counted or sometimes that certain line item does need to be counted?

            Is this in list view or is it a portal (looks like a portal)?

            You could make a second field, doesn't have to be on the screen but it probably should, that would put 54 in that field from you example (format it red).  Then your calc could add up all the numbers in No. field, and subtract the numbers in this second field.

            • 3. Re: Conditional Calculations?
              WilliamMartin_1

              I did not create the invoice layout, but It looks like it's just a ton of fields all titled differently - like "Garment1" "Garment 2" "Sex1" "Sex2" etc. I'm 100% open to reworking this to make it work better, if you can point me in the direction of some good information.

              The reason We can't leave the "No." field blank is kinda industry specific - so let's say a customer is getting 212 shirts made, and needs 100 of them to be folded and bagged. Folding and bagging adds to the total cost, but it shouldn't change the "Total Pieces". We've had customers complain about this - "Why does it say I'm getting 312 shirts when I only ordered 212?". So you may wonder why not just leave the "No." field blank and just put the total cost into the "Cost" - The reason for that is just transparency. It allows people to see what they're paying "A La Carte" Which is one of the things people like about working with us.

               

              My idea was - and feel free to tell me if this is not a good idea - to use an if function that would translate to the following in english: If "Garment / Paper" says "Bloop" subtract "No." from "Total Pieces".

               

              Thanks again. 

              • 4. Re: Conditional Calculations?
                WilliamMartin_1

                Hey Steve,

                To answer your questions - The idea was that there would be a drop down list, and some of the items on the list (We'd determine these, I could explain but I doubt you care about screen printing) would always be ignored by the "Total Pieces" calculation. So like I said above, if "Bloop", or more realistically something like "Fold + Bag", was selected the "No." would be ignored.

                 

                I like your idea of making a field off screen with the total value to be subtracted, that seems to make sense to me more than all of this nebulous "If" stuff, but I'm still not sure how to make FM understand that "Fold + Bag" goes into that field, but "American Apparel Tr301 Tank" does not.

                 

                Thanks!

                • 5. Re: Conditional Calculations?
                  philmodjunk

                  Not a good idea. You could end up continually revising your calculation to handle different descriptions scorrect as the descriptions may need to change from time to time.

                  You need to understand the current design of your database before you try to modify it.

                  A typical invoicing system (purchase orders and bills of materials are much the same) looks like this:

                  Customer----<Invoice---<LineItems>-----Products (and/or services)

                  Customer::__pkCustomerID = Invoice::_fkCustomerID
                  Invoice::__pkInvoiceID = LineItems::_fkLineItemID
                  Products::__pkProductID = LineItems::_fkProductID

                  For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                  Youd set up a layout based on Invoice with a portal to LineItems where selecting a Product ID in the _fkProductID field looks up data from Products.

                  With that Setup, I'd add a field to Products and LineItems that designates a given product as whether or not to include in the count. This field in LineItems would look up the value form the corresponding field in Products. (And I would treat "Folding and Bagging" as another product (actually a service) in the same products table. This would make the calculation field method that I describe possible.

                  For a working example of this set up of tables and relationships you can check out the Invoices starter solution that probably came with your copy of FileMaker. In it, the "lineItems" table is named "invoice data" but the relationships are the same as what I show here.

                  • 6. Re: Conditional Calculations?
                    WilliamMartin_1

                    The problem I see with your example of is 100% due to nature of our business. We are CONSTANTLY adding to the list of products, which is why the guy who set this up opted to keep the data entry very manual - choosing from an editable list instead of referencing some sort of master list that includes pricing and products. Our prices change on a daily basis due to the fluctuating cost of our distributors - so maintaining that information on an external table (not sure if that's the right term) would be nearly impossible as it stands. As an aside, I've pitched the idea that we go to a more set in stone pricing system but that doesn't seem like it's going to happen - at least not yet. 

                    I have a firm understanding of how our invoice works, and very very little of it is based on external information. It's basically just a WYSIWYG Excel form, doing some basic math based on numbers and information we type in on the spot.

                    Am I missing the point of what you're saying? I'll be the first to admit that a lot of the information regarding relationships goes way over my head.

                    • 7. Re: Conditional Calculations?
                      philmodjunk

                      I'm afraid that you are missing the point.

                      A products table IS an editable list. And the price can be looked up from this table or manually entered--whichever works better for your business model. It would not be an external table, just another table in your database. You have to put that list somewhere so you might as well manage it in a products table.

                      The key difference is NOT setting up a set of individual fields (or repeating fields) for listing this data as it will greatly complicate the overall function of your database.

                      • 8. Re: Conditional Calculations?
                        WilliamMartin_1

                        Thanks PhilModJunk, I appreciate your help.

                        I'll look into it and see what I can do!