7 Replies Latest reply on May 26, 2010 1:53 PM by philmodjunk

    A few questions from an Access convert

    Hoggster

      Title

      A few questions from an Access convert

      Post

      Hello,

       

      i'm a new convert to FMP 11 from Access. I really like some of the features but am having a really hard time duplicating some things that were really easy on access. If I may ask more than one thing;

       

      how do I add an auto date field to a layout (that is independent of any records in the assoc table) that will always display the current date?

       

      i know that to a certain extent that 'find' replaces queries in access. but... i have 2 related tables, one contains patient data including the ASA score. the other table just includes the ASA score (like a lookup list). when entering the ASA score in the patient tabe i'm using a drop down value list taking the ASA number from the ASA table.  how to I then sum all of the occurences of a particular ASA score occuring in the patients table and display it in the ASA table next to the relevant score (i hope this is clear)

       

      to clarify; Patients::ASA looks up values in ASA::ASA

      what i want is the number of times Patients::ASA equals each value stored in ASA:ASA

       

      i've got heaps more questions but i'm also enjoying figuring things out for myself but these 2 are really niggly.

       

      cheers

       

      Hoggster

       

        • 1. Re: A few questions from an Access convert
          Hoggster

          Dont worry about the ASA count as i've figured that one at last.

           

          One to add though.

           

          I have a table of addresses for companies. I want  to print address labels and have used the label wizard but i want a sheet that is all of one address. the wizard just gives a single label for each record.

           

          cheers

           

           

          • 2. Re: A few questions from an Access convert
            ryan

            to always display the current date:

            create a calculation field, which we'll call "current_date_c", and set the calculation to "Get ( CurrentDate )" and set the result to "Date"

            • 3. Re: A few questions from an Access convert
              philmodjunk

              how do I add an auto date field to a layout (that is independent of any records in the assoc table) that will always display the current date?

               

              Option 1: enter layout mode and enter // whereever you want today's date to appear

               

              Option 2: Define an unstored calculation field with this calculation: Get ( CurrentDate )  (Since this calculation returns the system clock date no matter what record you are on, it'll work here for what you want.

               

              For your second question, I think you have this relationship:

               

              Patients::ASA = ASA::ASA

               

              Define a calculation field in the ASA table as Count ( Patients::ASA ) and it will return the count of all patients records with a matching ASA value. I'm not sure if that's what you want here. You may want the count only for a specified Patient, that can be done also with a bit of work.

               

              I've done a fair amount of design work with Access as well as Filemaker. Here's a few other notes that may help with the transition:

              In Access you have sub-forms and sub reports. In filemaker, you can use a Portal for the same effect.

              In Access, you frequently work with record set objects. In filemaker, each table occurrence in your relationship graph has a current record, found set and sort order, you can frequently use those like you would a record set object in Access.

              In access, changing a field's name in the table definition will often break the solution. This is not the case in Filemaker unless you've used one of the few specialized functions or script steps that reference a field by its name.

              Missing the script debugger that's so handy in Access? Beg, buy, or steal a copy of filemaker pro advanced. It's script debugger fills the same purpose for filemaker.

              • 4. Re: A few questions from an Access convert
                philmodjunk

                I want a sheet that is all of one address

                 

                You won't be able to do the whole thing with the layout wizard.

                 

                You can:

                 

                Duplicate a record enough times so that the entire page of labels prints the same address--messy, but it's quick to set up.

                 

                Put a global field or block of global fields on the label layout. This layout can point to a utility table that doesn't store your records. Duplicate records to fill the page, copy your address data to the global fields and print. (With a global field, each record will show the same data, in this case your address.)

                 

                Set up a form layout that fills an entire page. Duplicate the address fields while in layout mode over and over, positioning the fields to align with the layout of your label page. For a 30 label layout, that'd mean putting down the same set of fields in 10 rows with 3 columns. Printing one record prints one page of labels.

                • 5. Re: A few questions from an Access convert
                  Hoggster

                  Wow now thats what I call quick.

                   

                  Thats the date thing sorted.

                  I figured out the ASA thing exactly as you had described :-)

                  the labels thing is a bit messy.I think for this one task I can keep with the access/word mail merge option which is a breeze (its only something i use every now and then)

                   

                  i'm sure i will have more questions as i try to duplicate the functionality of my access database but i'm completey reassured that there are people to help.

                   

                  a big thanks

                   

                  cheers

                   

                  Hoggster

                  • 6. Re: A few questions from an Access convert
                    philmodjunk

                    Filemaker can export to .mer (MS Word merge format) files also.

                    • 7. Re: A few questions from an Access convert
                      philmodjunk

                      Actually, setting up  a layout that prints an entire page of labels all from one record is not that difficult. Here's a demo file that uses a simple approach to do it:http://www.4shared.com/file/ENnngEvD/One_Page_Label_Demo.html

                      Simply select a person from the drop down on the labels page and the entire page will show the same address. (Enter preview mode or print the layout to see the entire page.)