10 Replies Latest reply on Mar 11, 2013 7:25 AM by BZ

    Search in portal doesn't work with relationship

    BZ

      Title

      Search in portal doesn't work with relationship

      Post

           Hi,

           In de database I'm creating I have a problem.

           I have one main table where each record has an id from where I build a hierarchy. Each hierarchy level is a table containing the different possible values within that level. With an id and parent_id, where the parent_id is in relationship with the value id in the next table (the next hierarchical level). This all works fine.

           Now I've a seperate table containing alternative values including the id of the original value. In the layout based on the main table I created a portal which displays the alternatives available for that records (could be more than one because more values in the hierarchy could have alternatives). To make this work I created a calculation field in the main table which looks up all the values id's within the records hierachy. Also this works how I want is, so it's just to explain how my database is build up. I hope it makes sence.

           Now the problem. I also want to be able to perform a find on an alternative name, so it wil display the record containing the original value of that alternative. When I search within the portal I get the error message:

      "This operation cannot be performed because one or more of the relationships between these tables are invalid."

           Why does this not work, can anyone tell me, where could be the problem ?

        • 1. Re: Search in portal doesn't work with relationship
          philmodjunk

               I had a big argument in Report an Issue over that same error message. I was insisting that this is a bug and the FileMaker Powers that be were insisting that it wasn't angry

               I don't have enougth detail to be sure, but it sounds like you are using an unstored calculation field as a match field in one or more of these relationships. The relationship functions just fine in browse mode but fails to evaluate while in find mode and thus you get this error message.

          • 2. Re: Search in portal doesn't work with relationship
            BZ

                 Ah, didn't think of that yet.

                 I indeed have a calculation field which is unstored, but to try making it stored I get the following error:

            The calculation “alternatives_calc” cannot be stored or indexed because it references a related field, a summary field, an unstored calculation field, or a field with global storage.

                 While I'm not using other summary-, calculation- or global storage- fields the problem would be it references a related field. Maybe I'm just not awake anymore but I don't get what is meant by this.

            • 3. Re: Search in portal doesn't work with relationship
              philmodjunk

                   Any calculation field that references a field in a related table cannot be a stored calculation field.

                   To see if this is the cause of the error and to consider alternative approaches, I'd need a much more detailed understanding of the relationships used and the find that you are attempting to perform.

              • 4. Re: Search in portal doesn't work with relationship
                BZ

                     Here a schematic how the relationships are created. I simplified it a bit by removing field names and the remaining categories in the hierarchy. To be clear, in the cat. tables (the hierarchy) I have one more field in each table wich is called original_value. (forgot to unfold the box).

                     Hope this helps to understand what I try to create. If it would be to complicated to do what I want, the portal which displays the available alternatives in the main layout could be removed, it is way more important that I will be able to perform a find using an alternative name which will give me the records that are linked to this alternative name.

                • 5. Re: Search in portal doesn't work with relationship
                  philmodjunk

                       Are cat1, cat2, cat3 distinct separate tables or different Tutorial: What are Table Occurrences? with the same data source table?

                       Please post the definition for the alternatives_Calc expression--which does look to be the culprit here.

                       And then please give an example, starting with the layout that you use (on which of the above table occurrences is it based?) and including the criteria specified and the fields in which they are so specified.

                  • 6. Re: Search in portal doesn't work with relationship
                    BZ

                         The cat tables are separate tables.

                         The expression for the calc field is: cat1::id & ¶ & cat2::id & ¶ & cat3::id

                         The layout is based on the main table. I have a list with all records in the main table and I have a portal in the header of the layout which displays the alternative values related to the selected record. So far this is exaclty what I want, the portal I don't neccessary need thought, but nice to have.
                         What is important, what I nead is the ablility to find and value which could occur in one of the cat tables, but which could also be an alternative value. Preferably I want one search field where I enter a value and all the records containing that value are displayed. While I don't know yet how to do this I thougth of starting simple to use the excisting field in the main layout to use for searching. Which works for the values that occur in the cat tables. The only thing is I have to know in which of the cat tables (cat1, cat2, or cat3) the value occurs.

                         The same I wanted to try with the alternatives, so I know a value is an alternative so I perform a find using the portal "value" field (which is based on the alternatives table), but then the problem occors where I get the error as mentioned in my first post.

                         I hope it's more clear what I try to do.

                    • 7. Re: Search in portal doesn't work with relationship
                      philmodjunk

                           Please give an example of a find that generates the error message. Please include the actual data entered as criteria and identify the field into which you are entering that criteria.

                           What results do you want if that find were to work?

                           I can see that your calculation lists values from the related "cat" tables in a return separated list and understand how that field will funciton when used in a relationship, but the calculation is returning a list of values from the id fields and matching them to a field in alternatives named "parent_id"--which, just going by the field names, seems a bit contradictory here.

                           More questions:

                           How does Main::id get a value? Is this a unique value in Main?

                           Are you aware that you might have 3 or more records in Cat1 that are linked to the current record on your Main layout, but the calculation will return only the first related record from cat1?

                           What problem are you trying to solve by having three separate "cat" tables here?

                      • 8. Re: Search in portal doesn't work with relationship
                        BZ

                             Ok, I see some things are not quite clear and I first should also explain why the database is build like this, because it might not seem very logic.

                             De data I will be using in this database comes from an excisiting database exported as text files. All information is in one big table. For now to make the thinking a bit easier, let's,  what I called categories (cat1-3), now call generations and the values are names of parents and childs (I have only one parent field for each name so each child has only one parent). So in the original table the hierarchy is inside one table, wich a column called genrations where for each name is mentioned in wich generation the name belongs (gen1, gen2 or gen3). So each child name has and id and an parent_id which is link to the id of the parent. This is the same for the alternatives, an alternative name has an id itself and an parent_id which is the id of the name where it is an alternative from. This is the reason is sounds contradictory. Now I want to add data with a bunch of new fields for each child which is al the way down in the hierarchy, so the parents and grandparents of that child are not important for data entry, they are just important to know what the relationship of the child is.

                             I don't want to change the original data to much, because later I want to be able to add updates to my database using the same original database.

                             I want to create a layout which displays the whole hierarchy (so all the parents and grandparents) of a child including the newly added data (specifications) for that child. I thought this wouldn't be possible (maybe with a lot of scripting which I know only basically) using the one table, so I created a script to put all names with the generation name gen1 into one table and did the same for the other generations. That's why I have alle the seperate tables to build the hierarchy. The main table has the columns where the new data will be added and two id field. The main db_id is an unique id using a auto-increasing number, the main::id is the id of the child in generation 1. This also means (I think) there could not be 3 ore more records in cat1 (gen1) The child has only one parent so the calculation field just displays the whole hierarchy of that child.

                             So again, in the main layout I have all records, so all child names each with the parent names of the different generations. Now a child or parent could also be named by an alternative name. I made the portal to display all the alternative names within all generations of the selected record. Let's say the child is called John and has an alternative name Paul, a parent of the child in generation 2 is called Richard and has an alternative name Dred. So when I select the record of John, I want the portal to display Paul and Richard.

                             For the find I can search for John, the right record will be displayed. Now I also want to be able to find the alternative name Richard, which would return the record of John. Therefore I use the name field in the portal and search for Richard, that's when I get the error.

                             That's a whole story, it's a little difficult to explain but I hope now you understand what I did and what i try to do and hopefulle get an idea why the error occurs. Maybe I have to work with an entirely different approach, I am more that happy with suggestions.

                             Thanks so far for you help ! I appreciate it.

                        • 9. Re: Search in portal doesn't work with relationship
                          philmodjunk

                               Ok, your information confirms that this is precisely the issue I feel is a bug, but for which the FileMaker Inc. developers insist is not a bug.

                               There are a number of design issues to what you have. You've assumed some limitations on what Filemaker can do that aren't really the case, but these issues don't necessarily apply to resolving the specific issue of getting your find to work without an error. It is in fact possible to put all of this data in to one main table with linking tables that use a "many to many self join" to link successive generations. This would have the added advantage of being able to trace back more than three generations of predecessors. But that's a separate issue you can pursue later and in a new thread if you are interested.

                               First, let me repeat back to you my understanding of what you have just to make sure that I have correctly understood you.

                               1) For a given record in Main, cat 1 holds the name of the parent, Cat 2 holds the grandparent and Cat 3 holds the great grandparent.

                               2) Alternatives, lists alternative names for the current record in Main plus the given  (as listed in cat 1, 2 and 3) names of the parent, grand parent and great grand parent. alternative names for the forebears are not included as I understand from your example. (You omitted "Dred" in your example.)

                               Do I have that correct?

                               The simplest solution that does not require many changes to the design of your database would be to use a script that uses separate requests or the extend found set option to search for a specified name in multiple fields of the different tables involved. The one design change would be to add a text field to Main that lists all alternative names for that individual. The script can then search the name field, the alternative name list and also the name fields in each of the three "cat" tables to pull up a found set where the specified name appears in at least one of these fields.

                               If you have not used a script to perform a find before, studying the examples in the following thread will be a good starting point for where we go from here: Scripted Find Examples

                          • 10. Re: Search in portal doesn't work with relationship
                            BZ

                                 Thanks for you reply. You indeed understood correctly except for point 2, there I also want to display "Dred", sorry that was my mistake.

                                 For the solution this doesn't make a difference actually, and thanks for the suggestion to create this field with the alternative names. This is indeed the simplest and working solution. Sometime it's so easy to think way to difficult.

                                 I might also look into the "many to many self join" option later, but will open a new thread for that if neccesary.

                                 For now, I can continue and I want to thank you very much for all your help !!