8 Replies Latest reply on Aug 23, 2016 2:51 AM by ac311

    Calculation field cannot be indexed - Is there a work around?

    ac311

      I have a contact management solution that includes tables containing equipment data (equipment type, make, and model) the contact may own. I'd like to make it more robust by providing quote generation as well as invoicing capabilities. The quotes I'd like generate involve indicating the equipment at the contact's location. So I began an  invoicing solution (courtesy of Guy Stevens) the layout I have has invoice metadata with a portal that has line numbers for the items purchased or quoted.

       

      I'm trying to incorporate the make, model info into the line items portal using a dropdown select the  foreign ID of the contact's equipment while displaying values from a second field. The secondary field is a calculation field. The Calculation Field that contains System Type, Make, Model info in a single field. I get the following error: “C_UnitDescription” (Name of calculation field) will not work because the field “C_UnitDescription” cannot be indexed. Proceed anyway?”

       

      I looked on online and cannot find a solution that applies to my scenario. My only thought is to convert the calculation into a text field, but not sure how to do that? Any suggestions?

        • 1. Re: Calculation field cannot be indexed - Is there a work around?
          TSPigeon

          ac311:

           

          Thank you for your post!

           

          I am going to move this thread from the FileMaker Community Feedback Space, which is specifically for input on the Community itself, to the Discussions Space where you should receive even more views and potentially more advice!

           

          TSPigeon

          FileMaker, Inc.

          • 2. Re: Calculation field cannot be indexed - Is there a work around?
            philipHPG

            What is the calculation on c_UnitDescription? An unstored calculation can not be indexed and there are a few situations where a calculation can not be stored: most commonly, uses a related table or uses another unstored calculation. I don't see anything in your description that makes me think that c_UnitDescription should be unstored.

            • 3. Re: Calculation field cannot be indexed - Is there a work around?
              philmodjunk

              I suggest describing your set up in more detail. Of particular interest will be the tables and relationships that make up your basic invoice and where in those tables you have this calculation field and where the fields that it references are located.

               

              The error message you describe is typically one you see when you try to use an unindexed field on the "many" or "child" side of a relationship--but I'm making a guess here and that doesn't tell you how to solve it as we'd need more info to do that.

              • 4. Re: Calculation field cannot be indexed - Is there a work around?
                tgellar

                If all fields are in the same table, and all fields are populated manually, change the calculation field to a text field, click Options, click Calculated Result and you should see the calculation. If not, then enter it here. Then click OK, and uncheck "Do not replace existing value...".
                Now, the result only changes whenever you change one of the listed fields in the calculation. And the result is indexable.

                • 5. Re: Calculation field cannot be indexed - Is there a work around?
                  philmodjunk

                  Now, the result only changes whenever you change one of the listed fields in the calculation. And the result is indexable.

                   

                  If the referenced fields are from a related table, changes to them will not trigger an updated calculation of the value. There is a way to do this with auto-entered calculations, but first we need to see if that is even necessary here.

                  • 7. Re: Calculation field cannot be indexed - Is there a work around?
                    ac311

                    Screen Shot 2016-08-23 at 12.48.59 AM.png

                     

                    I hope this makes my question a bit clearer. apologies for the murky posting.

                     

                    There is more to the database than what you see above. However, the tables that are involved in with the issue at hand are above in the illustration  and on the list below.

                     

                    Elsewhere in the database (Contact Layout), I set up a situation with multiple levels of drop-downs whereby the user chooses a system Type on the first drop-down. The second drop-down only the provides the makes that correspond to that system type will. When a make is chosen, only models under that make-category appear on the third drop-down.

                    The Unit table combines system type, make and model into one table using the foreign keys SystemType, Make and Model, which are then related to the contact.  It also contains the calculation C_UnitDescription. The calculation itself is as follows:

                     

                    Model_UNIT::Rating & " " & Model_UNIT::Units & " " & Make::Make & " " & Model_UNIT::Model & " " & SystemType_UNIT::SystemType

                     

                    My goal was to place UnitID Foreign Key on the InvoiceDetails table which is where the line numbers are and create a dropdown that displays the unit description. Unfortunately that is where the error I described on the original post appears.

                     

                    Ultimately the goal is to have the option to choose remaining units on the lines below the first item. So if three Unit records exist for that contact and I've already selected one, I'd like to select the second or  third on the next line.

                     

                     

                    Tables List and Relationships

                    Make_INVOICES       (SystemTypeiDFK = SystemTypeID)   SystemType_INVOICES

                    Model INVOICES      (SystemTypeIDFK =SystemTypeID) SystemType_MODEL_INVOICES  Make_Model_CV_Invoices

                    Model INVOICES     (MakeIDFK=MakeID)    Make_Model_INVOICES

                    Model INVOICES      (SystemTypeiDFK = SystemTypeIDFK)    Make_Model_CV_INVOICES

                     

                    Invoices      (ContactIDFK=ContactID)    Contacts_INVOICE

                    Contacts_Invoice      (ContactID = ContactIDFK)       Unit_INVOICES

                    Unit_INVOICES         (SystemTypeIDFK = SystemTypeID) Make_UNIT_INVOICES

                    Unit_INVOICES        (MakeIDFK = MakeID)     Make_UNIT_CV_NVOICES

                    Unit_INVOICES     (SystemTypeIDFK=SystemTypeIDFK)               Make_UNIT_CV_INVOICES

                    Unit_INVOICES     (ModelIDFK = ModelID)                            Model_UNIT_INVOICES

                    Unit_INVOICES (SystemTypeIDFK= SystemTypeIDFK AND MakeIDFK = MakeIDFK )                              Model_UNIT_CV_INVOICES

                     

                    Unit_INVOICES    (ContactIDFK = ContactIDFK AND C_UnitDescription= C_UnitDescription)                  Unit_INVOICES_CV

                    • 8. Re: Calculation field cannot be indexed - Is there a work around?
                      ac311


                      Screen Shot 2016-08-23 at 12.48.59 AM.png

                       

                      Hi phil,

                      I'll post the response I gave to the other philpHPG. I hope it answers your question.

                       

                      I hope this makes my question a bit clearer. apologies for the murky posting.

                       

                      There is more to the database than what you see above. However, the tables that are involved in with the issue at hand are above in the illustration  and on the list below.

                       

                      Elsewhere in the database (Contact Layout), I set up a situation with multiple levels of drop-downs whereby the user chooses a system Type on the first drop-down. The second drop-down only the provides the makes that correspond to that system type will. When a make is chosen, only models under that make-category appear on the third drop-down.

                      The Unit table combines system type, make and model into one table using the foreign keys SystemType, Make and Model, which are then related to the contact.  It also contains the calculation C_UnitDescription. The calculation itself is as follows:

                       

                      Model_UNIT::Rating & " " & Model_UNIT::Units & " " & Make::Make & " " & Model_UNIT::Model & " " & SystemType_UNIT::SystemType

                       

                      My goal was to place UnitID Foreign Key on the InvoiceDetails table which is where the line numbers are and create a dropdown that displays the unit description. Unfortunately that is where the error I described on the original post appears.

                       

                      Ultimately the goal is to have the option to choose remaining units on the lines below the first item. So if three Unit records exist for that contact and I've already selected one, I'd like to select the second or  third on the next line.

                       

                       

                      Tables List and Relationships

                      Make_INVOICES       (SystemTypeiDFK = SystemTypeID)   SystemType_INVOICES

                      Model INVOICES      (SystemTypeIDFK =SystemTypeID) SystemType_MODEL_INVOICES  Make_Model_CV_Invoices

                      Model INVOICES     (MakeIDFK=MakeID)    Make_Model_INVOICES

                      Model INVOICES      (SystemTypeiDFK = SystemTypeIDFK)    Make_Model_CV_INVOICES

                       

                      Invoices      (ContactIDFK=ContactID)    Contacts_INVOICE

                      Contacts_Invoice      (ContactID = ContactIDFK)       Unit_INVOICES

                      Unit_INVOICES         (SystemTypeIDFK = SystemTypeID) Make_UNIT_INVOICES

                      Unit_INVOICES        (MakeIDFK = MakeID)     Make_UNIT_CV_NVOICES

                      Unit_INVOICES     (SystemTypeIDFK=SystemTypeIDFK)               Make_UNIT_CV_INVOICES

                      Unit_INVOICES     (ModelIDFK = ModelID)                            Model_UNIT_INVOICES

                      Unit_INVOICES (SystemTypeIDFK= SystemTypeIDFK AND MakeIDFK = MakeIDFK )                              Model_UNIT_CV_INVOICES

                       

                      Unit_INVOICES    (ContactIDFK = ContactIDFK AND C_UnitDescription= C_UnitDescription)                  Unit_INVOICES_CV