5 Replies Latest reply on Feb 18, 2011 10:25 AM by philmodjunk

    General search bar on Welcome layout that searches and returns results from all of the tables



      General search bar on Welcome layout that searches and returns results from all of the tables


      I have a shared database on a server that other FM users access over the Filemaker Network with 15 or so tables, with many records in each. Currently I have a "Welcome" layout that acts as my "home screen" for users to start their use with the database:

      1) How do I get it so that the Welcome layout is always the first thing the User sees on login?

      2) How do I create and implement a general search bar in the layout that will search all of the tables in the database that will return the results of the find in a results window that the user can then navigate to directly from the homescreen?


        • 1. Re: General search bar on Welcome layout that searches and returns results from all of the tables

          1) Create a script that has the lines Enter Browse Mode and Go to Layout [Welcome Screen].  In the file options set it to run at start up.  When you're there you may see that one of the options is to 'Go to layout' on start up, but don't use that - you will inevitably want to add to the start-up script later.

          2) I don't know of any way of searching across all tables, unless the found records would only possibly exist in one of the tables.  I don't know of any way of combining the results from multiple tables into one listing.

          I'm always curious why people want to do this.  What search criterion could I have that would be valid to look for in 15 tables?  If it is Company Name, for example, and they might be stored in the Customer Table or the Supplier Table, then why not have all those records in one table, Contacts, and tag them as type 'Supplier' or 'Customer'?  If the criterion is a date, what date could be valid across 15 totally different tables?

          I'm sure you'll have a good answer, but this question always makes me wonder should the 15 tables be better managed as a lot fewer.

          • 2. Re: General search bar on Welcome layout that searches and returns results from all of the tables

            Not splitting up very similar data into separate tables is one of those things developers learn to avoid with time and experience. Using finds, layout features, record level access controls, relationships and scripts to work with different groups of data from the same table usually avoids the need for such separate tables.

            If you really have to do this, you have to set up a script that follows an outline like this:

            Perform find separately on each table

            Import the records from each table into a report table on which you base your report. If that's simple to do, then you probably don't need the separate tables in the first place.


            Devise a report layout with portals to each of the tables to be searched. Use relationships and/or portal filtering to show only the records you want for your report. There are lot's of potential problems to deal with if you use this option.

            • 3. Re: General search bar on Welcome layout that searches and returns results from all of the tables

              The reason this applies to our application is that we have things like companies, contacts, project documents, projects, jobs, quotes, PO's, bill of materials and parts and line items all as their own table because each of these concepts are either solid based on their own, or they are derived from one another, and mixing records in one table is like mixing apples and oranges - and actually makes efficient and sensible browsing of a database much easier. Another reason is that we have developed a sophisticated and intricate numbering system for everything in the database that is of uniform content (for example our numbering system can identity and categorize all of our concerned records by an indentifier in the form of a phone number - 004-500-1001 for example means - 004 - its a quote, 5XX - its an incoming quote, and 1001, its the 1001st quote to have come in). Because this numbering system is very useful for identifying and organizing vast amounts of data - and has a universal form - its a very elegant way of accessing what we want to access. Maybe from a pure database standpoint your points are correct, but most of the time, a user knows this number going in, and it would be a fairly nice feature to have, but not completely necessary I suppose.

              I appreciate the comments and advice, I'm curious why the find feature built into FM doesn't have a "Global search" option - I figured it would just be an easy thing to implement. Oh well, thanks anyway!

              • 4. Re: General search bar on Welcome layout that searches and returns results from all of the tables

                I suppose this takes us out of a technical disussion into the region of philosophy, but  some observations, for what they're worth:

                - I still don't really understand what search criterion you would look for that would be universally applicable over 15 different tables.  What factor is common to a Contact and a Purchase Order and a Bill Of Materials that a user would want to search for?  Would it not be confusing to search for a date, and return 3 purchase orders, 2 materials, and a quote?  One is the date raised, one is the date created, and the other is the date required (although you can't tell which is what, or whether the quote returned from that search is there because it came in on that date, or was returned on that date, or was required for that date)?

                - I worked for a company where every component had a unique 13-digit number.  There were a few gurus who understood how the number was made up, and could look at one and tell you it was a capacitor, used in the TXE4 exchange, built in London, and a whole pile more.  They couldn't tell you the final useful bit, of course, like what its capacitance or voltage actually was, but no mind - they got 75% of it right, and that was 75% more than the rest of us who... just read the Bill of Materials, where it listed everything we needed to know.

                - Those were the days when computer systems, and relational databases especially, were in their infancy.  Memory was expensive, processing slow.  Now, it is so easy to have everything uniquely identified (both humans and computers like that) but always show the friendly-front to the user. Why show them a number like '004-500-10011' where, if they consult their Rosetta Stone, or the Guru Of All Knowledge who can patronise them and say, "Son, when you've worked here 20 years like me, you too may be able to look at these numbers and know 'that's a Quote that probably dates from the early part of last year'. "  Why not just show them on the listing, on the screen, "Quote"?  Who cares that the quote I'm looking at and that I know is needed for tomorrow is the 1001th received this year?  What use can I put that to?  I didn't sort by that number to work on first-come-first-served (I hope): I found all the quotes not yet returned and sorted them by ascending due date, and started at the top of the list.

                - "Because this numbering system is very useful for identifying and  organizing vast amounts of data - and has a universal form - its a very  elegant way of accessing what we want to access."  I would suggest that it is only useful for the people who have acquired the job knowledge to have learned the numbering system, and is not nearly as elegant as looking at a record that says "Quote".  And "identifying and  organizing vast amounts of data" is exactly what Filemaker's job of work is, not a numbering system's...  I think your other description is better: "sophisticated and intricate".  (And if you want to retain your compiled-from-component-parts numbering system, Phil can tell you how to do that, anyway.)

                - Apples and Oranges have a lot in common.  They have a supplier, a case size, a weight per item, a bar code, a bought price, a selling price, a product manager, storage recommendations, etc.  All very different data, but the same fields.  Every time you improve or debug your database by calculating total shipment weight, or average profit margin, you add that work to one table, and every fruit benefits - you don't have to replicate the idea 15 times.  Then there are the couple of fields that the apples need that oranges don't need (and boy am I struggling here), there's: Apples_Need_Refrigerated = Yes, and Oranges_Need_TissueWrap = Yes.  But hold on, that's only a tiny fraction of the total fields.  Why not just have a field RefrigerationRequired = Yes/No/NA, and just tag it whichever way that fruit needs?  Or just mark it NA if it isn't applicable?

                - If there is a chunk of fields that apply to specific fruits only, then collect all those fields together and put them on their own tab on the data-entry layout, only ever consulted on the few occasions that an exotic fruit comes into stock.

                - If I want to see all the apples, I search in the Fruit Table for those records tagged 'Apple'.  If I want oranges, I look in the Fruit table for Oranges.  I don't have to remember the construct of a part number, nor even which of the 15 tables to go to (ok: I have to know fruit, but not whether it is on the 'Citrus' table or the 'Large Fruit' table.)  How is that less efficient or less elegant than exploiting one of Filemaker's strongest points - the intuitive ability to build really foucussed searches, instantly, using completely intuitive things like "Apple", ">2/1/2011", and "Yes"?  Filemaker will happily search millions of records in the twinkling of an eye.  I'm not sure where the 'efficiency' bit comes in via constructed part numbers.

                - When I find myself tempted to have 15 tables like you (sound like you) have here I ask myself am I confusing field requirements with data variation.  You could have a Product Table with 100,000 items in it, every one of which has a different Case Weight, but every one of which uses the field Case_Weight to hold the data.  Why does it matter if one is a boiled egg and the other is a Sherman Tank?  If I want to review all foodstuffs I look for the catagory 'Food'.  (Just don't look for 'Shell_Size > 50mm' or there could be fun.  (That was an Irish yolk.))

                No-one knows your DB and requirements better than you, and certainly I wouldn't suggest mixing Contacts and Purchase Orders with Bills Of Materials - but your original question was about searching for the same criterion across many fields in many tables because "they are broadly similar".

                • 5. Re: General search bar on Welcome layout that searches and returns results from all of the tables

                  Please note that I said "Not splitting up very similar data into separate tables..."

                  Obviously, this is not the case here.

                  It's possible that with those relationships, you can set up layouts and reports that combine data from related tables such that you can perform this search on that layout. That searches just one table, but allows you to enter criteria in related records. (Show me all the quotes for a customer named "john smith" where "john smith" is text in a related contacts record.)