9 Replies Latest reply on Nov 5, 2008 1:59 PM by TSGal

    How To Query?

    nolie

      Title

      How To Query?

      Post

      I am transitioning to Filemaker Pro from Access.  I don't need beautiful layouts I need to crunch numbers.  I just want to make Query's, make table Query's and Append Query's.  I have been taking a class online but it has nothing to do with number crunching it is all about layouts and making solutions for others.  Can anyone give me direction as to where to look or what to do in order to do these functions?

      Any help would be greatly appreciated I am more than a little frustrated. 

        • 1. Re: How To Query?
          TSGal

          nolie:

           

          Thank you for your post.

           

          When you create fields, one of the field types is a Calculation field.  This allows you to calculate information on a record (and in some instances, the entire database).  Also, there are Summary type fields.  These sum, total, average (minimum, maximum) records across a found set or a group of records.

           

          Find will let you find a set of records (Querys), and with Scripts, you can automate (and save) those Finds/Querys.

           

          If you have an example of what you want to do, I'll guide you through it, and that should help you for future querys.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: How To Query?
            nolie
              

            Awesome thanks for the help.

             

            I have a table guidelines with fields: program, vendor, region, rate, fee, amount.

             

            I have a second table Credit with fields: program, vendor, score, grade, rate_add, fee_add.

             

            What I want is the combined table with all occurrences. 

            • 3. Re: How To Query?
              TSGal

              nolie:

               

              Pull down the File menu and select "Manage > Database..."

               

              This is where you create your fields, tables and relationships.  Click the Relationships tab, and you will see your Guidelines and Credit tables.  If you want to combine them, there needs to be a key field that connects the two tables together.  I see the fields "program" and "vendor".  If you need both fields as the key, then click on the program field in one table and drag it to the program field in the other table.  When you let go, you will see a line linking the two tables together.  If needed, do the same for the vendor field.

               

              In a new layout in Guidelines, be sure to show the fields Program, vendor, region, rate, fee, and amount.  Also, at the top of the fields list, you will see the name of the current table (Guidelines).  This is a pull down, and select "Credit".  You can then select the fields score, grade, rate_add and fee_add.  Now you have all records that are linked.

               

              This should get you pointed in the right direction.

               

              Please let me know if should run into any difficulty.

               

              TSGal

              FileMaker, Inc. 

              • 4. Re: How To Query?
                nolie
                  

                I can do that but how do I show all the combinations?

                 

                If I am in one table I get one set of that table say 10 rows with the other tables data at the end.  What I need is the 50 rows of data with all the combinations of the first and second tables.   

                 

                I am building queries to compile new tables that can be used to model data.  I don't see how to make it build out.

                 

                Thanks in advance for the help.

                 

                 

                • 5. Re: How To Query?
                  TSGal

                  nolie:

                   

                  Thanks for the additional clarification.

                   

                  If you go into Layout mode (View menu), there is a portal tool appearing just below the oval.  This allows you to draw information on the layout and link to the other table.  You can show ten or more rows.  Include the fields that are not in the same table.  That is, if you are in the Guidelines table, the portal would show the linked information from the Credits table.  No need to show the Vendor nor Program field, since those are the key fields.

                   

                  If you want to build a new table, with each record matched up by key, then you may need to create a script that goes through each link, creates new records and replace the data from both tables.  Possible script steps would include Set Field and Import.

                   

                  If you need more specific information, provide me an example and I'll write up a sample script.

                   

                  TSGal

                  FileMaker, Inc. 

                  • 6. Re: How To Query?
                    nolie
                      

                    That is more than disheartening.  I asked lots of questions before I brought the software.  I have tables in Access when built at 1.9 Million rows.  In sql you create views and build tables from those, filemaker doesn't have that capability? 

                     

                    This seems more like a form making program on top of a database, am I missing something?

                     

                    I use a Mac so I wanted to go with FMP but I am thinking of switching to something else.

                     

                    Please advise.

                    • 7. Re: How To Query?
                      TSGal

                      nolie:

                       

                      FileMaker is a full functioning relational database; not just a form making program.  If you want to do a join, FileMaker will show you all related records, but it won't automatically create a new table and put the joined information in there.  You can write scripts that will accomplish the same task as a join, but it is not inherently built into the product.

                       

                      What are you attempting to do?  It may be easier and save space to not join the two files into a third file.  You can always summarize the data in the related records with the current records, so any math functions would be easier in FileMaker Pro.

                       

                      TSGal

                      FileMaker, Inc. 

                       

                       

                      • 8. Re: How To Query?
                        nolie
                          

                         

                        lender_codeGradeScoreCPoints

                        l113700-0.0025

                        l114680-0.0025

                        l1156600

                        l1166400

                        l1176200

                        l1186000.0125

                        l1195800.0125

                        l1205600.0125

                         

                        this is one table

                         

                         

                        lender_code     progcat     rate_code     Loan Amount   Rate      la_points       la_rate

                        L1                       VA1           170               30000          VA         0.750%         0.000%

                        L1                      VA15          140                30000         VA         0.750%         0.000%

                        L1                       VA3           174                30000         VA         0.750%         0.000%

                        L1                      VA30          110                30000         VA         0.750%         0.000%

                         

                        this is table two.

                         

                        Lender code is the common key.

                         

                        how can I get it to show all the combinations of the two tables where L1 has all the add ons for credit adjustments.  This is really simple in mysql, access and sql, I don't understand why I can't see it in Table View in Browse mode. 

                         

                        This is a many to many relationship.

                         

                        I must be missing something, sorry for my ignorance.

                        • 9. Re: How To Query?
                          TSGal

                          Nolie:

                           

                          Sorry for the late reply.

                           

                          In FileMaker, you can link two tables together through a "key" field, and then view all the information from anywhere.  However, FileMaker will not make a join file.  Instead, create a third table that has all the fields from both of your tables.  For this example, let's create a new table, "JOIN" with the following fields:

                           

                          lender_code

                          Grade

                          Score

                          CPoints

                          progcat

                          rate_code

                          Loan Amount

                          Rate

                          la_points

                          la_rate

                           

                          That is, this table has information from both tables.

                           

                          In addition, create a relationship on lender_code into both of your other tables.

                           

                          Also, create an additional field, a calculation field, with the formula: Count (<B table>::lender_code)

                          This will be necessary to make sure we match up the join.

                           

                          Create the following script:

                           

                          Go to Layout [<layout of this new table>]

                          Show All Records

                          Delete All Records [No dialog]

                          Set Variable [$counter; Value:1]

                          Loop

                             Import Records [No dialog;<the file where A table is located.; Add: Mac Roman]

                             Set Field [C::progcat; GetNthRecord (B::progcat; $counter )]

                             Replace Field Contents [No dialog; C::progcat; Current contents] 

                             Set Field [C::rate_code; GetNthRecord (B::rate_code; $counter )] 

                           

                             Replace Field Contents [No dialog; C::rate_code; Current contents] 

                             Set Field [C::Loan Amount; GetNthRecord (B::Loan Amount; $counter )] 

                             Replace Field Contents [No dialog; C::Loan Amount; Current contents] 

                             Set Field [C::Rate; GetNthRecord (B::Rate; $counter )] 

                             Replace Field Contents [No dialog; C::Rate; Current contents] 

                             Set Field [C::la_points; GetNthRecord (B::la_points; $counter )] 

                             Replace Field Contents [No dialog; C::la_points; Current contents] 

                             Set Field [C::la_rate; GetNthRecord (B::la_rate; $counter )] 

                             Replace Field Contents [No dialog; C::la_rate; Current contents]

                             Set Variable [$counter; Value:$counter + 1]

                             Exit Loop If [$counter > C::Count]

                          End Loop

                          Show All Records 

                           

                          ====

                           

                          This script begins by going to the new layout where the join result is displayed and removes all records.  A variable is then initialized to 1 that will be used to extract data from one of the tables.  Then, we enter the loop where we import the information from the "A" table, and then grab the first instance in the "B" table and replace those values across all the "A" values.  We increment the counter to 2, go back to the top of the loop, import the the same information from the "A" table, but now we extract the data from the second instance of the "B" table and replace again.  We continue this until the $counter variable exceeds the number of linked records in the "B" table.

                           

                          Using your example, we import the 8 records from "A" table, and then grab the first values from the "B" table and replace them across each of those 8 records.  We then import the same 8 records and get the second set of values from the "B" table.  This is repeated for the third and fourth times, and the loop is exited and all (32) records are displayed.

                           

                          If you need clarification for any of the above steps, please let me know.

                           

                          TSGal

                          FileMaker, Inc.