9 Replies Latest reply on Nov 18, 2009 7:41 AM by rotata

    New to FileMaker

    rotata

      Title

      New to FileMaker

      Post

      Hello,

       

      I have been a database creator/manager of several Access databases over the years. I also worked some with Oracle and with a few flat-file databases. I relied heavily on wizards to generate my code, tweaking the VBA and SQL by hand as required.

       

      Recently, I changed jobs and inherited a VERY complex FileMaker database with so many modules and relationships that it looks like spiders-gone-wild.

       

      Couple this with the fact that I've never designed in FileMaker and one can see that I am more than a bit overwhelmed.


      I need to make some modifications to the existing database (to correct some design errors), but don't have a clue as to where to start.

       

      At this point, a clue would be very helpful.

       

      System - XP

      Base - Filemake Pro Advanced 10.

       

       

      Thanks bunches! 

        • 1. Re: New to FileMaker
          KeithLarochelle_1
            

          We can help!

           

          We have a team of certified FileMaker Developers on staff that can remote into your system and help you as little or as much as you need.  Our FileMaker Support Services and technicians are all based in the US with local offices in San Diego and Chicago.  Feel free to give us a call as we would be happy to help!

          • 2. Re: New to FileMaker
            ninja
              

            Howdy rotata,

            Welcome to the forum.

             

            For us to help work you through things...we would need to know what you're trying to do.

            Insert tongue in cheek:


            At this point, a clue would be very helpful.

            Remove tongue from cheek ;)

             

            ################################## 

             

            Check out FMP training series literature.  Its pretty basic, but it will walk you through the basics in Filemaker-ese and may be all that you need.

             

            https://store.filemaker.com/US/ENG/RTL/product/view/group/TSM/

             

             

            Does this reply help?

            • 3. Re: New to FileMaker
              philmodjunk
                

              You might want to get a book on Filemaker.

              You can read the help files--they have their flaws but there's still a lot of useful info in there.

              Post specific questions here and we'll be glad to help.

               

              I've designed extensive database solutions in Filemaker and MS Access and can help "translate" some of the terminology for you.

               

              A few hints to get you started. Manage | Database | Relationships is a lot like the Relationships tool in MS Access but ALL relationships must be defined here it's not optional like it is in Access. You can look up this tool to learn what the buttons do (I really wish the filemaker engineers had at least given them tool tips, but they didn't!) in the help system. You can double click a relationship line to see the relationship details.

               

              When you find yourself wanting to add a sub-form or Sub-report, use a "portal" that's what filemaker calls the same basic object.

               

              Forms and Reports in Access have Record Source expressions in SQL. In Filemaker, you refer to a Table Occurrence (one of the boxes in the Relationship graph) and use the defined relationships to enable viewing/editing data in related tables. In Access, the record source for a form defines its Record Set. In filemaker you have a "Found set" which is all the records pull up the last time you performed a find.

               

              Forget SQL unless you plan to use ODBC to link to an SQL based database. Filemaker doesn't use it. In Filemaker, simple searches of the database are much simpler than doing the same in Access, conversely, specifying complex criteria can be a bit more challenging to set up in some cases.

              • 4. Re: New to FileMaker
                TSGal

                Keith Larochelle:

                 

                I have sent you a private message (top of this page - right side - X Messages).  Please read.

                 

                TSGal

                FileMaker, Inc. 

                • 5. Re: New to FileMaker
                  rotata
                    

                  Hi Everybody,

                   

                  Thanks for all the replies. 

                   

                  My immediate problem is that the db has several scripts for colorizing "maps" by certain groups and generates a legend for the map. The groups change every few months. Script one shows by "department"; Script two shows by "owner"; Script three shows "usage"; and so on. The scripts are basically identical, and utilize the same set of "sub-scripts" (sub-routine?). Each scripts looks at a specific field and the field may contain more than one item from a list (like having two owners). One of the scripts does not work right.

                   

                  On a floor with a couple dozen rooms - some rooms belong to Joe, some to Bob, and some to Tom & Andy. A few belong to both Joe and Tom & Andy.

                   

                  When I run the "Owner" script I get:

                  Legend:

                  Joe's room - Color 1

                  Bob's room - Color 2

                  Tom & Andy's room - Color 3

                  Joe's room / Tom & Andy's room - Color 4

                  The map should have only four colors. Joe has six rooms and all six rooms show as color 1, all of Bob's are color 2 and all of Tom & Andy's correctly show up as color 3. Unfortunately, if there are multiple "Joe's room / Tom & Andy's room" the generator makes a new color for each room. If they share ten rooms, then I get ten colors. Only one will show up on the legend. 

                   

                  The only solution I've found is to manually remove the carriage return, but that causes coding errors in another areas.

                   

                  How do I restrict the color for when there are two items selected from the list of "owners"? 

                   

                  Is there a problem with having an ampersand in a list? 

                   

                  Thanks again!

                   

                  • 6. Re: New to FileMaker
                    philmodjunk
                      

                    There's no way to tell just from your last post. We'd need to see the script and need to know how the relevant database tables and relationships have been set up.

                     

                    You can copy and paste a script to this forum most easily in one of two ways:

                    1. If you have Filemaker Pro Advanced, generate a database design report and copy the script from this report.
                    2. Print the script as a PDF document and copy the text from it.
                    • 7. Re: New to FileMaker
                      rotata
                        

                      Here is the script in question.

                       

                      Do you need to see the sub-scripts this script calls up?

                       

                      Thanks

                       

                       

                      Script Name Color Occupying Department

                       

                      Run script with full access privileges Off

                       

                      Include In Menu Yes

                       

                      Layouts that use this script

                       

                      Scripts that use this script

                       

                      Script Definition

                       

                      Script Steps

                      • Sort Records [ Specified Sort Order: Space::Bldg; ascending Space::Rm; ascending ] [ Restore; No dialog ]
                      • # Sets all values to their start points.
                      • Set Field [ Space::~Temp Criteria Table; "Calendar Present::" ]
                      • Set Field [ Space::~Temp Criteria Field; "Location|2 Occupying Department" ]
                      • Set Variable [ $$Color; Value:1 ]
                      • Go to Record/Request/Page [ First ]
                      • Set Variable [ $$Bldg; Value:Space::Bldg ]
                      • Set Variable [ $$Floor; Value:Space::Floor ]
                      • # Begin Map coloration
                      • Loop
                      • # $$OccupyingDept is native to this script. Modification of this variable to fit desired criteria should allow for other coloration patterns.
                      • Set Field [ Calendar Present::~Temp Trial Disp; List(GetField(GetAsText(Space::~Temp Criteria Table&Space::~Temp Criteria Field))) ]
                      • Set Variable [ $$OccupyingDept; Value:Space::~Temp Unit Code Disp ]
                      • Perform Script [ “Assign Color” ]
                      • Perform Script [ “Assign Legend” ]
                      • Go to Record/Request/Page [ Next; Exit after last ]
                      • End Loop
                      • Perform Script [ “Refresh All Maps” from file: “Maps” ]
                      Fields used in this scriptScripts used in this scriptLayouts used in this script Tables used in this scriptTable occurrences used by this script

                      Custom Functions used by this script Custom menu set used by this script

                       

                       

                      • 8. Re: New to FileMaker
                        philmodjunk
                          

                        "Do you need to see the sub-scripts this script calls up?"

                        Since the erroneous output appears to be either an incorrect legend or an incorrect color assignement, then yes, both of these subscripts:

                         

                        Perform Script [ “Assign Color” ]

                        Perform Script [ “Assign Legend” ]

                         

                        need to be posted.

                        • 9. Re: New to FileMaker
                          rotata
                            

                          Here are the two scripts. The first, Assign Color, is rather long. I appreciate you taking the time to look at them.

                           

                           

                          Script Name    Assign Color
                          Run script with full access privileges    Off
                          Include In Menu    No
                          Layouts that use this script   

                          Scripts that use this script   

                              * Color by Floor
                              * Color EID Occupants
                              * Color PI Admin Workgroup
                              * Color Actual Department
                              * Color Official Department

                          Script Definition
                          Script Steps   

                              * If [ Space::~Temp Criteria Table="Calendar Present::" ]
                              * # If this criteria has been colored before in another record, use that color.
                              * # NEW PARAMETER: If the color value is white, replace it.
                              * If [ IsEmpty(Space Colors::ColorValue)=0 and Space Colors::ColorValue ≠ "w" ]
                              * Set Field [ Space::ColorValue; Space Colors::ColorValue ]
                              * Exit Script [ ]
                              * End If
                              * # If $$Color exceeds the maximum number of colors, reset to zero
                              * If [ GetAsNumber($$Color)>GetAsNumber(80) ]
                              * Set Variable [ $$Color; Value:1 ]
                              * End If
                              * # If this criteria has not been colored before in another record, use the next available color.
                              * Set Field [ Space::ColorValue; $$Color ]
                              * Set Variable [ $$Color; Value:SerialIncrement($$Color;1) ]
                              * # The colors below are NOT used by Maps. This is for colors that are presently too dark.
                              * If [ $$Color=2 ]
                              * # 2 Blue
                              * Set Variable [ $$Color; Value:SerialIncrement($$Color;1) ]
                              * End If
                              * If [ $$Color=12 ]
                              * # 12 Indigo
                              * Set Variable [ $$Color; Value:SerialIncrement($$Color;1) ]
                              * End If
                              * If [ $$Color=18 ]
                              * # 18 Gainsboro
                              * Set Variable [ $$Color; Value:SerialIncrement($$Color;1) ]
                              * End If
                              * End If
                              * #
                              * If [ Space::~Temp Criteria Table="Space::" ]
                              * # If this criteria has been colored before in another record, use that color.
                              * # NEW PARAMETER: If the color value is white, replace it.
                              * If [ IsEmpty(TEST Floor::ColorValue)=0 and TEST Floor::ColorValue ≠ "w" ]
                              * Set Field [ Space::ColorValue; TEST Floor::ColorValue ]
                              * Exit Script [ ]
                              * End If
                              * # If $$Color exceeds the maximum number of colors, reset to zero
                              * If [ GetAsNumber($$Color)>GetAsNumber(80) ]
                              * Set Variable [ $$Color; Value:1 ]
                              * End If
                              * # If this criteria has not been colored before in another record, use the next available color.
                              * Set Field [ Space::ColorValue; $$Color ]
                              * Set Variable [ $$Color; Value:SerialIncrement($$Color;1) ]
                              * # The colors below are NOT used by Maps. This is for colors that are presently too dark.
                              * If [ $$Color=2 ]
                              * Set Variable [ $$Color; Value:SerialIncrement($$Color;1) ]
                              * End If
                              * If [ $$Color=12 ]
                              * Set Variable [ $$Color; Value:SerialIncrement($$Color;1) ]
                              * End If
                              * End If
                              * #
                              * If [ Space::~Temp Criteria Table="People Current EIDs::" ]
                              * # If this criteria has been colored before in another record, use that color.
                              * If [ IsEmpty(People Colors::ColorValue)=0 ]
                              * Set Field [ Space::ColorValue; People Colors::ColorValue ]
                              * Exit Script [ ]
                              * End If
                              * # If $$Color exceeds the maximum number of colors, reset to zero
                              * If [ GetAsNumber($$Color)>GetAsNumber(81) ]
                              * Set Variable [ $$Color; Value:1 ]
                              * End If
                              * # If this criteria has not been colored before in another record, use the next available color.
                              * If [ IsEmpty(People Current EIDs::EID)=0 ]
                              * Set Field [ Space::ColorValue; $$Color ]
                              * Set Variable [ $$Color; Value:SerialIncrement($$Color;1) ]
                              * # The colors below are NOT used by Maps. This is for colors that are presently too dark.
                              * If [ $$Color=2 ]
                              * Set Variable [ $$Color; Value:SerialIncrement($$Color;1) ]
                              * End If
                              * If [ $$Color=12 ]
                              * Set Variable [ $$Color; Value:SerialIncrement($$Color;1) ]
                              * End If
                              * Else If [ IsEmpty(People Current EIDs::EID)=1 ]
                              * Set Field [ Space::ColorValue; "w" ]
                              * End If
                              * End If

                          Fields used in this script   

                              * Space::~Temp Criteria Table
                              * Space Colors::ColorValue
                              * Space::ColorValue
                              * TEST Floor::ColorValue
                              * People Colors::ColorValue
                              * People Current EIDs::EID

                          Scripts used in this script   

                          Layouts used in this script   

                          Tables used in this script   

                              * People
                              * Space
                              * Space

                          Table occurrences used by this script   

                              * People Colors
                              * People Current EIDs
                              * Space
                              * Space Colors
                              * TEST Floor

                          Custom Functions used by this script   

                          Custom menu set used by this script   

                           

                           

                          =========================================================================

                           

                           

                          Previous Script: [Assign Color]    Parent Folder: [Newfield]    Next Script: [-]
                          Script Name    Assign Legend
                          Run script with full access privileges    Off
                          Include In Menu    No
                          Layouts that use this script   

                          Scripts that use this script   

                              * Color EID Occupants
                              * Color PI Admin Workgroup
                              * Color Actual Department
                              * Color Official Department

                          Script Definition
                          Script Steps   

                              * # The Legend table is linked to Space by criteria, building abbreviation and floor.
                              * # If a location's criteria has not been added to this floor, this script will add it.
                              * If [ Space::~Temp Criteria Table="Calendar Present::" ]
                              * If [ IsEmpty(Space Legend::~Temp Legend)=1 ]
                              * Set Field [ Space::~Temp Legend; $$OccupyingDept ]
                              * End If
                              * End If
                              * If [ Space::~Temp Criteria Table="People Current EIDs::" ]
                              * If [ IsEmpty(People Legend::~Temp Legend)=1 and IsEmpty(Space::~Temp Legend EID Checksum)=0 ]
                              * Set Field [ Space::~Temp Legend; Space::~Temp Legend EID Checksum&" ("&Calendar Current EIDs::EID|Office&")" ]
                              * End If
                              * End If

                          Fields used in this script   

                              * Space::~Temp Criteria Table
                              * Space Legend::~Temp Legend
                              * Space::~Temp Legend
                              * People Legend::~Temp Legend
                              * Space::~Temp Legend EID Checksum
                              * Calendar Current EIDs::EID|Office

                          Scripts used in this script   

                          Layouts used in this script   

                          Tables used in this script   

                              * Calendar People
                              * Space
                              * Space

                          Table occurrences used by this script   

                              * Calendar Current EIDs
                              * People Legend
                              * Space
                              * Space Legend

                          Custom Functions used by this script   

                          Custom menu set used by this script