1 Reply Latest reply on May 6, 2011 4:39 PM by philmodjunk

    Upgrading from Access Questions



      Upgrading from Access Questions



      I'm trying to redo an Access app in FM Pro.  I'm quite new to FM, so need to know if I can do these things.  The Access app is for awarding financial aid to college students.

      1:  There are multiple schools in a Schools table, with an auto-incrementing ID column.  The user starts the app and chooses a school and a fiscal year (09-10, 10-11, etc).  This becomes the "active" school and year.  Everything done thereafter is relative to those items.  I guess I need to create a layout to display available choices and allow user to pick them, and a "current data" table to store the chosen values to, so during the session they are available.  Is this right?

      2:  The app is multi-user.  Several people may be in the app, each working on a different school.  How would I set up this table mentioned above so each user has their own default school and year?

      3:  The app requires that after choosing the active school and year, I write some information into the Windows registry, in known registry keys.  How can I do that with FM Pro?

      4:  The app requires obtaining a data record from an external Access database.  The criteria values to obtain this record are readily available on my FM Pro layout.  The question is, can I build an access query to get that record and populate various field values into the active layout?

      5:  On the active layout, there are several fields which have to do with student income.  There is also a field which is for the financial aid awarded.  I must populate that field based on the other fields, which are added/subtracted to get "total income".  The Awards table has 3 columns: Year, Max Income, Award Amount.  I need to find the first record in the Awards table where Year = "active year" and Max Income >= the "total income" calculated previously.  Basically, finding the right record based on income in the table.  All of this is the formula/lookup for the calculated "financial aid awarded" field on the active layout.

      There will be many more issues to resolve, but it seems like these 5 are show-stoppers if they can't be resolved.  Can anybody answer one or more of these questions, so I can learn how to accomplish them in FM Pro?


        • 1. Re: Upgrading from Access Questions

          1) Look up how to create and use fields with global storage. You can have your user select/enter a school and a year in these global fields and then the values will be accessible throughout your solution. You can also use global variables to hold data for such purposes, but if the user will be entering/selecting the data you need to store like this, a global field is usually the better option as you can directly edit it's value on a layout.

          2) Yes, definitely use global fields for this. With global fields, each user will have their own copy of these values and the values they choose will not be visible to the other users. (and FileMaker does a much better job of locking users out of records that another user is editing than FileMaker.)

          3) That's a good question. I don't know how you'd do that one. You might need a plug in to do that. As you probably know, manipulating the registry can be a dangerous thing if you don't know exactly what you are doing. If you can do that with a batch file, I suspect you can use send event to run the batch file.

          4) You can export to excel and import from the excel file into FileMaker. There are also ways to set up an ODBC link to the Access file, though I haven't tried to set that up myself.

          5) You can define a relationship that uses total income to link to your table dynamically such that changes in the look up table automatically update or you can use a looked up value option to copy the data into fields of the current record--an option to use if you want a "snapshot" of the current values and you don't want them to update automatically. It all depends on what you need here. There are also ways to set up scripts that would use your total income to perform a find or you can set up a portal (think subform from access) to use total income to display data from a matching table.