1 2 3 Previous Next 33 Replies Latest reply on May 15, 2012 8:54 AM by digital-carpentry

    Searching within a portal--simple problem


      I used to design filemaker pro standalone databases as well as fmp databases that are served over the web. Then I took a 3 year hiatus and have seemingly forgotten some basic concepts. Now I'm looking at a database that I didn't complete and am having an embarressingly difficult time figuring out why I can't get it to perform a simple search correctly (any search that finds at least one match comes back with all records, though a null set will return a 'no match'). I'm quite sure my previous self as well as most of you would know within seconds what is wrong with it, but my current self cannot figure it out. I know that I didn't previously establish the relationships within the database because all of that was to be coded in the web interface (which never happened). So, I probably just don't have a relationship specified correctly.


      Anyway, if anyone is willing to take a look at it (via email), I'd be greatly appreciative.


      On a separate but related note, I've been using filemaker advanced 9, but is my understanding correct that if I updated to 12 and Go that I could achieve portability on iphone and iphone devices without having to build a web interface? It's this assumption that leads me to make this work as a standalone file.



        • 1. Re: Searching within a portal--simple problem

          hi Mark,


          I'd be happy to take a quick look if you wanted to post the db or provide a connection to it. But maybe there is something else going on. Are there account restrictions, fields not set to find, something else withholding the find? Is there a script involved for the find, or a script that has replaced the standard find function (custom menus)? Have you tried a Quick Search? Do you know for certain that data matches the search criteria? Just trying to throw out a few ideas that might help.


          Updating to 12 does provide for some very nice design features and FMGo is free to work with v12, so there's lots of cool things you can do with a hosted db. Do keep in mind however that upgrading to 12 is not reversible and no client under 12 will be able to connect.





          • 2. Re: Searching within a portal--simple problem

            I see your title mentions a portal, Are you trying to do a find using fields that are in the portal, and not getting the expected results?


            Remember that the find you are doing is based on the table you are in, not the portal table, so I've noticed at times you may try to find based on 2 fields in the portal, expecting to get only results where both fields are in the same portal record, but that won't neccesarily be the case, you would get records in the current table that have that data in ANY row of the portal, not neccesarily the same row.  If this is the case you may want to switch to the other table, do the find for records that match then come back to related records from there.

            • 3. Re: Searching within a portal--simple problem
              Stephen Huston

              And there are problems when performing a search in a portal for "null" or "=" in the related record set

              (i.e. It is  more accurate to do an Omit search on >=0  (zero) to find records which lack portal records).


              If you are searching in a portal and getting unexpected results, provide some details re your search structure.

              • 4. Re: Searching within a portal--simple problem

                Thanks for the replies, everyone.


                My solution is a database of plants that users can search for on the basis of about 40 different fields simultaneously.  Users can search (not add/edit) for plants, create Lists of fav plants, and save the Searches.  This will be a standalone database for fp12/go (though currently I'm desiging in this in 9adv).


                The main tables right now are Users, Lists, List-Plants, Plants, and Searches, with Plants having by far the most data.  I could use some advice on setting up the relationships (which I didn't do before since I was originally doing this thru Lasso).


                So, my FIND problem was definitely the within-portal search (not restrictions or special scripts, etc).  If I do this in a list-view (on a Plants layout) it's fine.  Why I chose to have a portal instead of a Listview is not clear to me now, though I tend to think that I must have had a good reason.  For some reason I have an "Interface" table that is entirely for the purpose of sorting in the portal, so perhaps I can eliminate that. 


                I think I need three sets of table occurrences:


                1.  The basic plants Table, for admins to add/edit records.  This TO line might include a Lists TO table for pre-made lists that most users would utilize ("nitrogen fixers"). 

                2.  The UserLists TOs (Users/UserLists/UserListPlants/UserListsPlantsPlants).  This one is straightforward. 

                3.  Saved Searches.  This one is not clear to me.  It contains numerous fields that would be filled-in by the user (wind tolerant, drought tolerant, etc) for searching on.  It's not clear to me how this would relate with the Plants table or if it needs to.  I suppose I could set up dozens of relationships between the two tables but I assume I'm performing a find wherein the user would fill in a form (saving the search) and those values would be sent as variables in a script to fill in a Plants based layout where the Find would be performed.  This is the one where I was previously sending the variables to the Interface layout with a Plants portal, so going straight to a Plants layout page in list view seems like the way to go, and using some script out there to deal with the sorting. Hope this is clear enough. 


                Does having these 3 TOs seem like the proper way to set this up?  Any thoughts on how to set up #3? 


                One separate question: I'm making my list views have graphics instead of text values (sunny/partlysunny/shady) so I currently have a table related to EACH field in the plant table, with 3-5 container fields with images in each of those tables.  Is there a better way to do that than have so many tables?

                • 5. Re: Searching within a portal--simple problem

                  I don't see the need for 3 SETS of table occurrences, you'll need one occurrance for each table, then possibly some others to facilitate special relationships, but I would not see 3 SETS.


                  I'm not sure that all the tables are completely needed, but let's look at what we have.


                  1.  Plants is the big one, and definitely needed

                  2.  Lists (not sure what you are aiming for here, depending on the number of lists you are wanting, this could probably be done with a category or key word list stored in the plants table

                  3.  List-Plants This may not be needed depending on what you are using these lists for, it sounds like this is getting rather complicated just for a basic category or keyword list.

                  4.  Users, I assume this is for storing some user access information or customized lists, depending on what all you are doing with here, this may not be needed for a standalone database, how many users are you looking at?  If this is mainly for creating customized lists, this could be done within the list table itself.

                  5.  Searches,  Again, it's not clear everything you are doing here, but this could be just part of the lists, unless new data is being entered into the Plants table, a search based on the same information would just return the same results, so just creating a list from the found set might be a better route.


                  The "separate question" Basically you have a table with 1 record holding the container fields to store your graphics, and relate to this as a constant (basically a field in the table that always = 1 and a field in the Containers table that = 1 Then just calculate your display information {if(TEXT="sunny", Containers::SunnyGraphic, etc)}


                  Your relationships will basically be mainly from Plants to the Lists, possibly including the Current User to accomodate User Specific Lists.

                  • 6. Re: Searching within a portal--simple problem

                    Thanks for the thorough reply. 


                    Just to fill in my explanation a bit more, while there would be lists that relate simply to the Plants table (everything marked as a nitrogen fixer or everything marked as a drought-tolerant species), I also want to give the users the ability to create whatever lists of plants they want.  Those lists may emerge from more complex searches, or just who knows...  I think of it like iTunes....some smartlists just keep all of the Beatles in it, while other manually-made lists are user-preferred mixes.  So, that's why I would assume I would need Lists and List-Plants.  I assume people would not create too many lists--maybe a dozen, not a hundred. 


                    It's true I may not need users, but I feel like it's a good thing to build it in from the start. 


                    Searches seems necessary because I assume that people may want to go back to a search they performed before without having to try to re-enter all the fields again.  But yes, creating a list from the found set is the same thing as saving the search terms and redoing the search from the user's perspective, and does seem simpler from my end.  I hadn't thought of that.  I'll have to think about that for a while and write more later.


                    Container fields:  Rather than saying 'sunny' or 'droughtolerant' they have 1-5 values in separate fields (1-5 sun, 1-5 drought), so I'd just expand the if statement to include the fieldname and the value.  Great solution!!  Thank you!

                    • 7. Re: Searching within a portal--simple problem

                      Yes I assumed you'd have to expand the IF statements to fit multiple results, I just didn't want to type it all out!


                      On the Lists, I would create a record for each list in the List Database, and in a text field create a list of Plant ID numbers.  Then relate that text field to your Plant ID field in the Plant Table

                      This would do away with the need for a Link table (Plant List) and you could build the custom user List/Search the same way.  Just have a script loop through your foundset and create the text list in a variable or global field, then jump to the List table and create a new record using the results.


                      Your field would probably not be shown on user layouts, but would look like this:







                      Of course this list would include only 4 plants, but your lists would be longer, and would just be longer text fields.


                      For Customized User Lists, just have another field that stores the UserName and you can access these through a separate relationship that includes the Current User matching the List UserName.



                      Bill Pelfrey

                      Digital Carpentry



                      "Let us build a foundation for your business"


                      Phone: 606-305-9004

                      email:  wdpelfrey@gmail.com


                      For Easy Web Hosting check out:   http://www.bizland.com/join/index.bml?AffID=684960&LinkName=email

                      • 8. Re: Searching within a portal--simple problem

                        I wouldn't have thought of that. 


                        Besides having one less table and fewer records, is there any other advantage to doing it this way?  And when doing it this way, is hardreturn preferrable to commas?  Is there anything wrong or limiting in doing it with the ListPlants table? 

                        • 9. Re: Searching within a portal--simple problem

                          Yes you need to use a return in order to use this for a relationship.


                          The main benefit is cutting out another table, and it's easier to build add a new list, you are only adding one record rather than having to create one record per plant as you would with the link file.

                          • 10. Re: Searching within a portal--simple problem

                            This would mean you would not need the List Plants table, just the List table and the Plants Table.

                            • 11. Re: Searching within a portal--simple problem

                              I'm trying to work out your suggestion for the container images:

                              Basically you have a table with 1 record holding the container fields to store your graphics, and relate to this as a constant (basically a field in the table that always = 1 and a field in the Containers table that = 1 Then just calculate your display information {if(TEXT="sunny", Containers::SunnyGraphic, etc)}

                              Ok, so which two tables are you talking about: Containers and Plants?  The containers table has one record with many container fields (named "sunny1" and "sunny2" and "windy1" and "windy2", etc...)  Do I understand that correctly?  If so, I'm not clear how I go about translating the Fieldnames and Fieldvalues in the Plants table (Field=Sunny, Fieldvalues=1,2,3,4,5...; Field="Soil" Fieldvalues="loam, clay, sandy") into the [IF(TEXT=] statement above.  Seems like there should be some way for it to detect the field and values so I wouldn't have to type out the hundreds of possibilities into an IF statement.  

                              • 12. Re: Searching within a portal--simple problem

                                The problem with putting the PlantIDs into hardreturn lists right now is that the plants db doesn't have data in it.  so, the lists need to be dynamic, not static.  It would need to perform a Find everytime. 


                                Right now I have some pre-set searches so that users can get an immediate list of common things like "nitrogen fixers".  But the script I have for this (see attached) is not elegant.  Seems like there should be a way for the ListName [which would be equiv to the Fieldname in Plants, and which I've set as the ScriptParameter] to become the searchfield without me telling it to do that for every field.  Seems like I can only put a $variable into the value part of the argument.


                                At this point I'm probably going to have many questions so perhaps I should ask them to the group. 

                                • 13. Re: Searching within a portal--simple problem

                                  the file won't attach for some reason...

                                  • 14. Re: Searching within a portal--simple problem
                                    Stephen Huston

                                    Try zipping the file, then use the Advanced editor (top right grey text of the compose box) for composing, where there's a browse button for selecting files to attach.

                                    1 2 3 Previous Next