8 Replies Latest reply on Dec 22, 2014 7:54 AM by rt314@icloud.com

    Excessive Runtime in Conditional Value List

    rt314@icloud.com

      Title

      Excessive Runtime in Conditional Value List

      Post

      Hi all,

      My question pertains to an excessive runtime to populate the second list on a two-step conditional value list.

      I recently split my db into the separation model, and I’m going through to make sure everything works as it should. I have a conditional value list where the first value list is a global field of options from “Distribution Type”. The second value list is from “Analysis Info” where the available options are based on the foreign key matching the parent key in the global field. I’m attaching a picture below for easier understanding.

      The second value list is chosen to include only related values starting from “Distribution Entry 4”, which is an occurrence of “Distribution Entry”. The relationship between “Analysis Info” and “Distribution Entry 4” says that DistributionTypeIDfk=globalfield, AnalysisCreatedBy=DistributionCreatedBy, and there’s also a date limiting inequality component to the relationship.

      Any thoughts on what is causing a long runtime in the UI file?

      -I made sure all fields involved are indexed (except the global of course).

      -I can select and commit other fields not related to these, and it doesn’t have any affect (so that’s why I think it’s a runtime issue).

      -In the data file, or in my original single file before separating, the second value list populates immediately. However, in the UI, it takes anywhere from 5 to 20 seconds to populate the value list.

      Thanks!

      DB_question_copy.jpg

        • 1. Re: Excessive Runtime in Conditional Value List
          philmodjunk

          Your written description is very different from what I see in your data model.

          The relationship between “Analysis Info” and “Distribution Entry 4” says that DistributionTypeIDfk=globalfield,

          Actually, it does not. There is no match up of DistributionTYpeIDfk between Analysis Info and distribution Entry 4, what you have is a "two stage" relationship from Distribution Entry 4 to Distribution Entry to Analysis Info. And gCurrentUser is the only global match field specified in those two relationships that link these two table occurrences. You also have a field named gDTypeSelection that is named like a global field but the "crows feet" in your screen shot show that it is not a global field as that would result in the "T" connector not crow's feet.

          -In the data file, or in my original single file before separating, the second value list populates immediately. However, in the UI, it takes anywhere from 5 to 20 seconds to populate the value list.

          Are both UI and data files already open? In some cases, especially if externally authenticated server accounts and FileMaker accounts are both in use, that delay can be due to the external data source reference opening the second file and authenticating the user.

          • 2. Re: Excessive Runtime in Conditional Value List
            rt314@icloud.com

            Thanks, Phil. I spent a lot of time writing a narrative then cut a lot out when I decided to post the pic, so I accidentally wrote the labeling wrong. Sorry about the confusion on that...go by the picture if there's confusion.

            The gDTypeSelection was originally a global field, but changed that for functionality purposes. I just tested it as a global field and that doesn't fix anything...It will present the appropriate options for the current value, but that's only because that global value is already selected. When I switch to a different option in the value list, the conditional list still shows the same values as with the originally selected global option. Also, the gDTypeSelection field doesn't do anything other than act as a filter for the conditional value list. I use the _DTypeIDfk value from 'Analysis Info' to pull in all the associated 'Distribution Type' information.

            Are both UI and data files already open?

            They weren't, but I opened them both and this did not help. I'm running both files from the same folder on the same computer.

            Could there be a problem with how I am relating the "created_by" fields between tables that takes a long time to finding the appropriate fields?  Thanks!

             

            • 3. Re: Excessive Runtime in Conditional Value List
              rt314@icloud.com

              I just removed other relationships that filter down the value lists, but that did not help either.

              • 4. Re: Excessive Runtime in Conditional Value List
                rt314@icloud.com

                I don't know if this is related or not, but if I log into the UI file with an account, then close the file and log into the UI file with a different account, the data file is still using the first login credentials for a short while (10 - 20 seconds). Is this normal or possibly a similar issue?

                • 5. Re: Excessive Runtime in Conditional Value List
                  rt314@icloud.com

                  PhilModJunk or anyone else...thoughts? Or suggested work-arounds to do it differently?

                  • 6. Re: Excessive Runtime in Conditional Value List
                    rt314@icloud.com

                    So turns out I had my relational filter wrong...I added a second occurrence of "Analysis Type" and related the _DTypeIDfk from it to gDTypeSelection of Distribution Entry. That's interesting that it worked prior to splitting into the UI and Data files. Thanks for challenging and believing in me that I could figure it out on my own! :)

                    P.S. I used this thread to figure out what I did wrong, particularly the example file: http://forums.filemaker.com/posts/9a187beb92?commentId=200113#200113 . So thanks Phil for all your continuous help!!

                    • 7. Re: Excessive Runtime in Conditional Value List
                      philmodjunk

                      Sorry that I couldn't get back to you on this but I just didn't have the time to puzzle out how your data model was intended to work with your given layouts. Glad you got it figured out.

                      BTW, I've replaced that list of forum links with a single Demo file that covers all of the topics in that set of links and adds new examples as well. This is all in a working .fmp12 file so you can see how they work and read information on how they were set up and how they work:

                      Adventures in FileMaking #1 - Conditional Value Lists

                      • 8. Re: Excessive Runtime in Conditional Value List
                        rt314@icloud.com

                        Thanks Phil! I'm actually glad you didn't spend the time ( and appreciate the time you did spend ) to figure it out since I was able to figure it out. I don't know why I set it up that way. Thanks for the updated link and all you do! I've only posted twice on here, but you've answered so many more questions I've had in other posts!