1 2 Previous Next 18 Replies Latest reply on Apr 17, 2014 1:33 AM by disabled_dconnect

    How can I create a table relationship that is based on contains a value rather than equals a value?

      Title

      How can I create a table relationship that is based on contains a value rather than equals a value?

      Post

           Hi,

           I'm sure I've been advised this before; however, I doubted my FM skills and that made me shy away from asking more.

           How do I create a relationship between two tables to create a portal that filters results based on its table's relationship with the table layout it is nested in.

           I can easily do this for equals to, does not equal too; however, I have no idea how to create a relationship between tables that is based on contains.

           My filters currently use equations like this:

           not IsEmpty ( FilterValues ( staff table main table 2::staff reference number ; staff table main table::staff reference number that this staff member knows) )

           Basically, this equation filters shows only members of staff that the current member of staff (i.e., the record I'm viewing) has a relationship with. I use this type of filter to ensure if they have a relationship with staff number 10 staff number 100 is also not shown.

           Many thanks

        • 1. Re: How can I create a table relationship that is based on contains a value rather than equals a value?
          philmodjunk

               What you describe is a "portal filter" not a relationship. wink

               For "contains this text" filters, use the PatternCount function.

               PatternCount ( PortalTable::Name ; "Apple" )

               will filter your set of related records down to records with text in the Name field such as:

               Apple
               Applesauce
               Green Apple
               Dappled

               You can also use the Left function if you want to use the specified text as a "starts with this text" filter.

          • 2. Re: How can I create a table relationship that is based on contains a value rather than equals a value?

                 Hi Phil,

                 Yes. I have a portal filter at the moment, and it works very very slow. You taught me that it is better to have table relationships rather than filters. And I changed some of my filters such as PortalTable::Name =  AnotherPortalTable::Name and things to a table relationship where both are related by this field. This worked a lot quicker and I was really grateful :)

            My question is it possible to set up a table relationship that is not based on two fields equaling each other, but that the content of one field is contained within the content of another field.

                 Thanks.

            • 3. Re: How can I create a table relationship that is based on contains a value rather than equals a value?
              philmodjunk

                   You have options other than =, but "contains" is not one of them. You're options are either a portal filter or Execute SQL with the LIKE keyword. (You can use ExecuteSQL to pull up a return separated list of ID's to use as a match field.

                   The other option is to use a calculation field, possibly with a recursive custom function to produce a return separated list of possible match values in your target table. But this is mainly practical for "starts with" partial matching as a calculation field can produce a set of values in a field such as:

                   AppleSauce
                   AppleSauc
                   AppleSau
                   AppleSa
                   AppleS
                   Apple
                   //and so forth....

              • 4. Re: How can I create a table relationship that is based on contains a value rather than equals a value?

                     Thanks Phil,

                     Okay so that's a no.

                     It's just I remember you telling me that my tables should not take 15 seconds to load, and even if I've got a filter on a table with 50000 records, you know people with 5000000 records and they don't take 15 secs to load.

                     How do I go about working out why they are loading so slowly, by loading I mean that when I am in one layout and go to another layout with a  filter in it it can take 20 secs for the new layout to appear as the filter takes a while to do its thing.

                     I used to get this with other filters with the equals filter but they load instantly with a  table relationship in place of the filter equals relationship.

                     In regard to the SQL and calculation. Is SQL route going to speed things up + what is the learning curve of setting this up as I've not delved into SQL yet. The calculation field I don;t think will work as I am looking for things like the first field has the word director in it and for the record to show up in the filter this work needs to be contained in the related field, i.e., it shows up if the field is "operations director in charge of finance"

                • 5. Re: How can I create a table relationship that is based on contains a value rather than equals a value?
                  philmodjunk

                       I don't know if SQL would produce a faster response or not. Testing would be needed against large record sets to see if there is a significant difference or not.

                       There are many factors that can affect how fast a layout updates:

                       1) Is the file local to your computer, hosted from another computer on the network or hosted from another computer located outside the network? How fast is your network/internet access?

                       2) are you using a web browser to access the database?

                       3) are you using an iOS device to access the device?

                       4) What unstored objects are located on your layout that need to evaluate before data can display on your layout? (conditional formatting, filtered portals, summary fields, unstored calculation fields--especially those that compute aggregate values such as Sum() from large sets of records....

                       5) large, high resolution graphics on your layout will also slow things down--especially in a list view or portal where you may have multiple instances of the same container field displaying on your screen.

                       6) Are any scripts performed when you access the layout? And might such a script perform a find or sort records? Does such a find or sort refer to a unstored or unindexed field?

                       With regards to 1-3 above, response times will rank from best to worst as: Local, Local Network, external to network, with the remote host outside your network being much slower than the other two. Web Browser and iOS clients will be slower than FileMaker Pro clients.

                       Normally, the only place you can usually make improvements are in 4 and 5 and those boil down to three basic strategies:

                       Reducing the size of your found set to an absolute minimum.

                       Avoiding finds and sorts that refer to uindexed fields over large numbers of records. An example of that is if you need to specify two criteria for a find: a date in an indexed date field and also a number in an unstored calculation field, you can perform a find for all records with that date and then return to find mode and use the number in the unstored calculation field to constrain the found set. With millions of records in your table, the two stage find can be many times faster than a single find specifying criteria in both the date field and unstored calculation field.

                       Simple, "plain vanilla layouts". You may want to make duplicates of your layout and chop out major portions of the layout in the duplicates and then test them to compare how fast they update. This can identify what features are the key "drag" on your system and may help you focus in on changes that make the layouts pop up quicker. You may also want to use the getThumbNail function to produce lower resolution copies of your images that still display acceptably on the screen but take much less time to display.

                  • 6. Re: How can I create a table relationship that is based on contains a value rather than equals a value?

                         Hey Phil,

                         Okay I won't get into SQL then, if you're not sure if it will improve matters, seems like a learning curve I can side step.

                         I'm going to look into point 4 deeper and get back to you.

                         As for the rest, I've a local file on my mac I work off.

                    • 7. Re: How can I create a table relationship that is based on contains a value rather than equals a value?

                           Hi Phil,

                           Looked at indexing etc and it is all indexed.

                           I've got an idea though, I've a lot of portals based on a field that has checkboxes with options, for example, A B C D E.

                           One portal will show all records with option A ticked one with B etc.

                           I could create a table instance that only shows records that do not equal X and use this table for all these portals.

                           I think it may work.

                      • 8. Re: How can I create a table relationship that is based on contains a value rather than equals a value?

                             Yes that worked, I set up a field with just x in it and created a relationship that only shows records that do not match this field.

                             I've an important question though phil.

                             I'm used to Excel, and had my early database there. On excel I used to filling each field in with an "x" as if i was sorting rows and I ever had a column with nothing in it then the row would become broken with the column on the other side of the blank column not sorting.

                             Question: is it fine just to have blank fields in filemaker rather than adding x, as I got used to using excel.

                        • 9. Re: How can I create a table relationship that is based on contains a value rather than equals a value?
                          philmodjunk

                               In general, I see no advantage to always putting "x" in a field as it's default value.

                          • 10. Re: How can I create a table relationship that is based on contains a value rather than equals a value?

                                 Thanks Phil,

                                 Okay, then I think I've worked out a way around this and fixing the slow filters.

                                 Last question: is there a way to remove all instances of fields that equal x in the database.

                                 There is no option in find and replace that searches for fields that equal x there is only match case or whole words?

                            • 11. Re: How can I create a table relationship that is based on contains a value rather than equals a value?
                              philmodjunk

                                   Replace Field contents can return those fields to their original empty state. One way is to perform a find that pulls up all records with just an x in that field, clear the field in one of the found records and then select Replace Field Contents from the Records menu...

                              • 12. Re: How can I create a table relationship that is based on contains a value rather than equals a value?
                                FentonJones

                                     First, I have an idea how to match for a word in text (about your earlier post). Relationships don't do a "contains" find (as far as I know). However they can do a match if the words it's looking at each on a line of their own. So if you turn every space " " into a return ¶, a word can be seen, no matter what else is in the text. If there are other characters, such as ".", "?", "(", ")", etc., then they should be removed. I think there are not all that many, so they be specified. But I think I'll use Filter instead.

                                      

                                     Let (

                                     txt = Filter ( Text ; "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ " );

                                     Substitute ( txt; " "; ¶ )

                                     )

                                      

                                     That would produce, for: Green Apple

                                     Green

                                     Apple

                                      

                                     This would be seen by a relationship looking for: Apple (well, for "green" also). It would not see: Applesauce

                                     So it is difference from the PatternCount () method, which sees inside the word.

                                      

                                     The above calculation field would be done in the same table as the field you want a portal to see. So it can be a Indexed field, for the relationship to use. [The field looking at it can be anything, Indexed or Unstored (such as a Global field).]

                                      

                                     Creating an Indexed field via the above calculation adds a little size to the database's file. But more a smallish amount of text, just a few words, it is not a big deal.

                                      

                                      

                                     Two, as Phil said, Replace Field Content is the command to change the text in a field, for all Records in the Found Set. So either be sure that you only have the records you want in the found set. Or write that command very well, with no mistakes. Clearing the field for the found set is the simplest method, so you should be OK. But otherwise, it a good idea to save the data of that field, for the found set, before running the operation.

                                      

                                     P.S. If I run an Replace Field Content on someone else's data, running on their server, I would do one or two things. I'd run it only one record, to make sure it was correct (sometimes not so :-\ ). And, sometimes, have a "save" field that I save the data from that field, for the found set, into the save field; in case something goes wrong, I can bring it back. But you're clearing it is OK, as there's little to go wrong (as long as you put your curser in the correct field). So the above is just a small warning.

                                      

                                     Question for Phil: Can FileMaker Pro 13 use Edit; Undo Typing to bring back the data the above operation? I kind of don't think so (since it goes thru the records of the found set, but maybe?).

                                • 13. Re: How can I create a table relationship that is based on contains a value rather than equals a value?
                                  philmodjunk

                                       Hi Fenton,

                                       I'm glad to see that you are still involved in the forum. I know that FileMaker 13 is supposed to offer more undo capability, but I've never tested it in the context of undoing a replace field contents operation.

                                  • 14. Re: How can I create a table relationship that is based on contains a value rather than equals a value?

                                         Hey everyone,

                                         Thanks for looking into this for me. I've been working through it today and found to issues already.

                                         First, can "However they can do a match if the words it's looking at each on a line of their own. So if you turn every space " " into a return ¶, a word can be seen, no matter what else is in the text."

                                    Be applied to numbers as well. I tried it and it didn't seem to work. I haven't tried it on words yet; however, I followed the instructions to the tee.

                                    Second, the workaround of one field doesn't equal a blank field in the table relationships doesn't work if the field is blank. I changed it to one field doesn't equal a "x" field and this works fine.

                                    1 2 Previous Next