12 Replies Latest reply on Aug 16, 2011 11:45 AM by Matty_1

    Sudden <Index missing> ...



      Sudden ...


      Hi there,


      I've been using a fully functional database, today I tried to make a few small changes.  Since the change I've been getting an <Index missing> on my transaction table who's relationship is based on the contract number with the contract table. Before the issue the relationship was a little more complex but I dumbed it down to contract number only and still I'm still returned an index missing error.


      Strangest thing is that the portal is still working properly on the contract side displaying all the associated transactions but when I go to the transaction slips I can't pull the contract price and commodity.


      I also tried shutting down FMP, still there.


      Is there a relationship debugger much like the script debugger?

        • 1. Re: Sudden <Index missing> ...

          <index missing> is telling you that the key field used to match values on the child or "many" side of your relationship no longer has the index that is needed in order for this to work. You'll need to look at the key field's storage options to see why it is no longer indexed. This often occurs if global storage or do not store the results is selected for the field.

          • 2. Re: Sudden <Index missing> ...

            Ok I found the root of the issue, another change I made which I forgot about was a change in the calulation field on the contract side.  I have a set of calculations which would determine the contract number.  Originally everything revolved around the client name (relationships and calulations) which had it's own field on the contract table and a value list that called upon the contacts. To avoid the issue of running into the same names of clients I switch the relationship to the clientID.

            The client field has now become a reference field pulling its data from the contacts based on which clientID is selected.  I then had to change my forumla for outputting contract numbers so that it pulled the client name from the contacts and no longer from a local field.  This caused it to no longer index itself.

            Do you have a suggestion to fix this?  Is the only solution a script tigger which assignes the client to the client field after a clientID is commited?


            Here is what my formula looked like BEFORE i switch it to reference the field on another table.

            Case ( IsEmpty (Client) ; ""; IsEmpty (Date) ; ""; IsEmpty (NumberOfLoads); "";

            WordCount(Client) = 1 ; Upper (Left(Client;2) & Right(Client;1)) & Right(Year(Date);2) & Month(Date) & Day(Date) & "-" & NumberOfLoads; WordCount(Client) = 2; Upper(Left(Client;2) & Left(MiddleWords(Client;2;1);1)) & Right(Year(Date);2) & Month(Date) & Day(Date) & "-" & NumberOfLoads; WordCount(Client) = 3; Upper(Left(Client;1) & Left(MiddleWords(Client;2;1);1) & Left(MiddleWords(Client;3;1);1)) & Right(Year(Date);2) & Month(Date) & Day(Date) & "-" & NumberOfLoads; Upper(Left(Client;1) & Left(MiddleWords(Client;2;1);1) & Left(MiddleWords(Client;3;1);1)) & Right(Year(Date);2) & Month(Date) & Day(Date) & "-" & NumberOfLoads)

            • 3. Re: Sudden <Index missing> ...

              For your relationship, define ClientID in the ClientTable as nothing more than an auto-entered serial number.

              For any table such as contracts where you need to link it in a one to one or one (client) to many (contracts) relationship use a relationship like this:

              Client::clientID = contracts::ClientID

              Contracts::ClientID should be simple number field (no auto-entered serial number for it in Contracts)

              If a contract can be linked to many clients and a client can have many contracts, let me know so I can elaborate as this would then be a many to many relationship which typically requires a third "join" table to serve as the link between the other two tables.

              The only reason I can see for using your current calculation would be to support an existing hard copy filing system or to provide compatibility with and external data system.

              • 4. Re: Sudden <Index missing> ...

                Yes this is how I currently have everything setup.  My current formula is as you pointed out being used to match the hard copy filing system.  I would like to stick to the same system which refers to the client name and now that the relationship is no longer based on the client it has become a reference field.  My calculation can no longer be index because it's calculation is based on a reference field.


                NOTE: I'm not sure if reference field is the correct term for what I'm describing (the field that have yellow light bulbs, pulling data from another table based on the relationship)  I'm self taught so I tend to mix up my terminology.

                • 5. Re: Sudden <Index missing> ...

                  Under what exact circumstances do you see "missing index"?

                  Normally, you should be able to add this field to an existing layout simply by adding the field and selecting it to be from an occurrence of the table where you defined the calculation field.

                  Perhaps this is part of a value list? If so, please describe your settings and intended purpose for your value list.

                  • 6. Re: Sudden <Index missing> ...

                    Sorry I think I didn't make myself very clear.  The missing index is because the sale transaction table is referencing the contractnumber field on the contract table.  The contractnumber field can no longer be indexed because within the contractnumber calculation the client field is being pulled from the CONTACT table and no longer local to the CONTRACT table.  Does that make sense?

                    The only solution I see is creating a new field on the contract table called ClientCopy and then setting up a scrpit trigger to assign it the appropriate cleint name when the ClientID is selected.  I'm just curious to know if there's a better method.

                    • 7. Re: Sudden <Index missing> ...

                      That doesn't make any sense. If the relationship is based on the new ClientID serial number. You should not have this trouble when accessing fields in the related table. This would only happen if your relationship used an unindexed field as a key when you check the relationship in Manage | database | relationships.

                      The only other way I can imagine you'd see this message is in a value list where this field provides one of the values in field 1 or field 2 of the value list.

                      So we need to put this one under a microscope here. Exactly what are you doing, step by step when you get <index missing> and exactly where do you see it?

                      • 8. Re: Sudden <Index missing> ...

                        I'm getting the issue because ContractNUMBER can no longer be indexed. (Atleast I think it's the issue based on whay you've told me) So the related table is giving an index missing message.

                        The problem is that FMP will not let me change the storage option back to indexed with the following calculation for the ContractNUMBER field:

                        Case ( IsEmpty (Contacts_Sales_Contract::Client) ; ""; IsEmpty (Date) ; ""; IsEmpty (NumberOfLoads); "";

                        WordCount(Contacts_Sales_Contract::Client) = 1 ; Upper (Left(Contacts_Sales_Contract::Client;2) & Right(Contacts_Sales_Contract::Client;1)) & Right(Year(Date);2) & Month(Date) & Day(Date) & "-" & NumberOfLoads;

                        WordCount(Contacts_Sales_Contract::Client) = 2; Upper(Left(Contacts_Sales_Contract::Client;2) & Left(MiddleWords(Contacts_Sales_Contract::Client;2;1);1)) & Right(Year(Date);2) & Month(Date) & Day(Date) & "-" & NumberOfLoads;

                        WordCount(Contacts_Sales_Contract::Client) = 3; Upper(Left(Contacts_Sales_Contract::Client;1) & Left(MiddleWords(Contacts_Sales_Contract::Client;2;1);1) & Left(MiddleWords(Contacts_Sales_Contract::Client;3;1);1)) & Right(Year(Date);2) & Month(Date) & Day(Date) & "-" & NumberOfLoads;

                        Upper(Left(Contacts_Sales_Contract::Client;1) & Left(MiddleWords(Contacts_Sales_Contract::Client;2;1);1) & Left(MiddleWords(Contacts_Sales_Contract::Client;3;1);1)) & Right(Year(Date);2) & Month(Date) & Day(Date) & "-" & NumberOfLoads)


                        The issue is on the sales transaction table called "Sales", I'm trying to pull the price and commodity from the contract.  The Sales table is related to the contract table called "Sales_Contract" via the contractNUMBER which is generated by a calulation.  The calcualtion takes the clients name, the date and number of loads and generates a contract number.  The Sales_contract table is related to the contacts via the clientID.

                        • 9. Re: Sudden <Index missing> ...

                          But the relationship is based on ClientID not Contract number right?

                          If you are using a contract number in a different relationship here, then it should also be a simple serial number based relationship--not this complex unstored calculation.

                          Any calculation that refers to fields from another table or record cannot be a stored calculation. That's a given and what I understood from the first, but that fact will not prevent you from "pulling data from another table" unless the relationship is using this unstored calculation field--which is something I understood that you were no longer doing here.

                          I think I need to see the actual relationships between each table. What fields are used and what operators.

                          • 10. Re: Sudden <Index missing> ...

                            The contractNUMBER field IS unstored because it's referring to a feild from another table.  My sales table returns the index missing because the relationship is based on an unstored calculation field.  

                            I understand what is causing the issue but I need to maintain the calculation because this is what is request by the owner in order to conform to a previous filing system.


                            So my question is no longer what is causing the index missing message, I got that after your first reply.  My question is now, how can I maintain the above formula so that I can index it once again.  The only solution I can think of it a script trigger but I'd rather another if at all possible.  I could be wrong but a script trigger seems like an unclean solution.

                            • 11. Re: Sudden <Index missing> ...

                              You can maintain this value, just don't use it as a key in your relationships. Use a serial number field for the relationship. Then you can refer to any field, stored or unstored in the related table  without encountering this issue.


                              I'll call your calculation filed cContractNumber and the serial ID field ContractID.

                              Define this relationship between "Transactions" and "Contract":

                              Contract::ContractID = Transactions::ContractID

                              If I want to see Contract::cContractNumber on a layout based on Transactions, I use the field tool to add a field to the layout, select Contract from the drop down and click ContractNumber to specify the ContractNumber field.

                              I now have ContractNumber displayed on my Transactions layout. To the user, it looks like the relationship is based on the unstored ContractNumber field, but it's really based on the ContractID field.

                              • 12. Re: Sudden <Index missing> ...

                                Of course, my bad.  Thanks a bunch Phil!