13 Replies Latest reply on Jan 28, 2011 11:38 AM by philmodjunk

    Using Multiple Tables / Portals and PHP - Simple situation, I need help

    ChipCustomizer

      Title

      Using Multiple Tables / Portals and PHP - Simple situation, I need help

      Your post

      I've been a light user or FileMaker Pro Server and setup an online database.  Here is the situation and the issue I have:

      I have two tables setup - one for a person, one for a job.  I have a portal setup to show what jobs a person has worked.  The person's ID is how the two tables are connected.  In it's simplest form I have:

      Person Table:
      Person Name
      Person ID

      Job Table:
      Person ID
      Date
      Job Name

      I have struggled with the following query both inside the FM Pro client and through the PHP interface.  I simply want to be able to run a query that says:

      Show me all People who haven't worked a certain Job Name in the past 30 days

      If anyone knows how to do this through the FM Pro client and through the PHP interface please let me know.  The PHP interface looks like it can only call up one table so I'm not sure how to ask the system to call both tables and put together a query like this to display on the web.

      I've been stuck on this problem for over 4 years if you can believe that - I just keep putting it off since I can't seem to figure that out.  If the back end of the database was SQL server, I would know how to compose the query but FM Pro has been a challenge for me regarding this.  Any help would be greatly appreciated.  If you happen to solve this problem for me and I implement it I'll send you a free Poker Chip Customizer ( http://www.chipcustomizer.com ) :)

      Thanks,
      Dan

        • 1. Re: Using Multiple Tables / Portals and PHP - Simple situation, I need help
          philmodjunk

          Shouldn't you have a third table so that you have Person, Jobs and WorkOnJobs?

          I can give you simple steps for how to do this with FileMaker client but someone else will have to provide the PHP equivalent.

          Enter find mode

          enter  a job name in the portal record

          Enter > and the date from 30 days ago

          Click the omit button and perform your find.

          If you specify the job name in a global text field, you can use this script:

          Enter Find Mode[]
          Set Field [Jobs::Job Name ; YourTable::GlobalJobNameField]
          Set Field [Jobs::Date ; "> " & Get ( CurrentDate ) - 30 ]
          Omit Record
          Set Error Capture [on]
          Perform Find[]

          • 2. Re: Using Multiple Tables / Portals and PHP - Simple situation, I need help
            ChipCustomizer

            PhilModJunk - great start.

            Here is a little more complexity though.  The person table has a field for active and a field for available.  I would like to tie the above solution in and restrict the results to only folks who are active and available.

            Essentially:  Show me all the active people who are avilable and havent' worked a certain job in over 30 days.

            To simplify things I'd like to make a button so that someone can just put in the job name and how many days they couldn't have worked it.

            For the online solution I know you can call a script from the web PHP, but how would you get input data into the script from the web?

            Thanks,

            Dan

            • 3. Re: Using Multiple Tables / Portals and PHP - Simple situation, I need help
              philmodjunk

              I don't know PHP enough to hazard even a guess on that part of your question.

              Enter Find Mode[]
              Set Field [Person::Active ; "Active" ]
              Set Field [Person::Available ; "Available"]
              New Record/Request
              Set Field [Jobs::Job Name ; YourTable::GlobalJobNameField]
              Set Field [Jobs::Date ; "> " & Get ( CurrentDate ) - YourTable::GlobalDaysWorkedField ]
              Omit Record
              Set Error Capture [on]
              Perform Find[]

              I've made some assumptions about yoru active and avaliable fields that may be incorrect. If so, you'll need to modify the script accordingly. This script, BTW, should be run from a layout based on Person.

              • 4. Re: Using Multiple Tables / Portals and PHP - Simple situation, I need help
                ChipCustomizer

                Thanks for the code - I tried it but the syntax isn't working.  I was able to do the following:

                Go to Layout["Main Form" (Person)]
                Perform Find[Restore]
                Constrain Found Set [Restore]
                Sort Records [Restore, No dialog]

                The first Find looks like this: 
                Action:  Criteria:
                Find Records   Person::Thursday: [Available]

                The second Constrain Found Set looks like this:
                Action:  Criteria:
                Omit Records    job::name:["thisjob"] AND job::date [>=9/1/2010]

                That seems to work - hard coded but it still works.  Ideally I would like to just perform a find and then click a button that pops up a window and makes the user choose a job and a day amount and then constrains the previously found set with that criteria.

                • 5. Re: Using Multiple Tables / Portals and PHP - Simple situation, I need help
                  philmodjunk

                  Can you explain what you mean by the "syntax isn't working"? This is a very commonly used scripted technique. It uses data entered in global fields and this opens up a way for the user to specify the criteria used in the find by entering data into those fields. (Fields must have global storage specified or the script will not work.)

                  • 6. Re: Using Multiple Tables / Portals and PHP - Simple situation, I need help
                    ChipCustomizer

                    Ok - pershaps I should back up a little here and example exactly what I'm trying to do to get this to work properly.  I've been searching and searching forums for help and too many people are assuming people know that when you find Perform Find[Restore] that we know exactly what's in the "Restore" area, hence the reason why I elaborated on my above post.

                    Now for the syntax, and global fields perhaps I'm missing the big picture here.

                    How does it all work?  For example, if I have a custom dialog box with two input fields, where and how are those used.  I am playing around right now trying to figure out how to take the hard coded solution I had and make it more useable. 

                    The only solution I have right now is a custom dialog box that asks for two fields, job and date.  It then just searches for that job and date.  Not what I need but at least I figured out a little on how the custom dialog box works.  Here is what I have so far on the custom dialog box:

                    Enter Find Mode[]
                    Show Custom Dialog ["Restrict found person by jobs worked";"comment";job::name;job::date]
                    If [Get(LastMessageChoice) =1]
                       Perform Find[]
                    End If
                    Exit Script[]

                    How would I tie in the Find with the Constrain to the solution above and make it flexible and not hard coded

                    I don't know much about global variables or why they would need to be used for this solution, but I'd like to take the above custom dialog box and simply do the search I need (which is show me peole who haven't worked a certain job since a certain day) and that are listed as available.

                    I'm simplifying the solution but in reality, I have on the Person table fields called Monday, Tuesday, Wednesday, ... and each are set to either Available or Booked or empty.  I need to search to see lets say all Females that have marked themselves available on Wednesday (I may need additional criteria, like age, type, car or whatever).  Lets say I have 600 people in the database and when I do my initial search I get 60 that fit the criteria.  Then I need to restrict those results to only the found people who haven't worked a certain job from a certain date.

                    Pretending I know absolutely nothing about FM Pro... which isn't too far from the truth.  Is this possible.  Can I make it easy so anyone using the database can do an intial search and then click a button to restrict the search to the constraints I need?  Should I do this a completely different way? 

                    My heads spinning a little here.  I have programming experience, but the logic on FM Pro doesn't fit to what I know as a programmer.  I really don't understand the need for a global variable here or if I can do what I want to do.  Sorry if I seem ignorant, but I am.

                    • 7. Re: Using Multiple Tables / Portals and PHP - Simple situation, I need help
                      ChipCustomizer

                      Ok I'm a little closer now but still need help trying to generalize it a little.  Here is what I have so far:

                      Enter Find Mode[]
                      Show Custom Dialog ["Restrict found person by jobs worked";"comment";job::name;job::date]
                      If [Get(LastMessageChoice) =1]
                         Perform Find[]
                         Show Omitted Only
                         Constrain Found Set [Restore]
                      End If
                      Exit Script[]

                      The Constrain Found Set [Restore]  contains Action:  Find Records Person::Monday[=Available]
                      Again, hardcoded, but at least the Job name and date aren't.  When I run the script I have to enter a date in as such:  > 1/1/2011

                      So basically I have partially what I need but I'd like to control which day of the week I want to search available for and see if I can also add in some other dynamic criteria.
                      Any help will be appreciated.

                      Is there more flexibility creating my own layout with the find criteria I need and global variables rather then using the Custom Dialog box?  If so I need help understanding how to set that up instead.

                      ...Dan

                      • 8. Re: Using Multiple Tables / Portals and PHP - Simple situation, I need help
                        philmodjunk

                        Do it this way:

                        Show Custom Dialog[//use the text and input fields that you need here]
                        Enter Find Mode[]
                        Set Field [Person::Active ; "Active" ]
                        Set Field [Person::Available ; "Available"]
                        New Record/Request
                        Set Field [Jobs::Job Name ; YourTable::GlobalJobNameField]
                        Set Field [Jobs::Date ; "> " & Get ( CurrentDate ) - YourTable::GlobalDaysWorkedField ]
                        Omit Record
                        Set Error Capture [on]
                        Perform Find[]

                        link the custom dialog's input fields to: GlobalJobNameField and GlobalDaysWorkedField

                        Also note that I don't have [Restore] anywhere in this script. I don't use that scripting method for the very thing that you complained about: when you look at the step, you can't tell what criteria is being used unless you click on a button to open up the script step in another dialog. I use set field steps to enter this criteria one field and step at a time so that I can see how a scripted find will work at a glance.

                        More on global fields: Create a test file and define two text fields. Set one up with Global Storage and leave the other as just a text field. Put these two fields side by side on a layout, enter browse mode and type some data into both fields. Now enter find mode. Note how the data in the local text field disappears so that you can enter some criteria in the field. Note that the data in the global field remains. That's why this script needs global fields. The data entered into them will remain accessible after the Enter Find Mode[] step takes the database into find mode so that the script can use this data to create and enter criteria in the local data fields.

                        The other advantage to global fields is how they function when your file is hosted over a network. Any data entered into a global field is not visible to any other user. Thus each user can enter data in these fields and run this script without their actions interfering with othe users.

                        • 9. Re: Using Multiple Tables / Portals and PHP - Simple situation, I need help
                          ChipCustomizer

                          PhilModJunk,

                          Thanks - perhaps we can take this off Forum for a few iterations until we get the complete solution.  Plus I'd like to get your info so I can send you a ChipCustomizer :)  The solution above would work for a criteria that was fixed - say Available and Active, but in reality, those fields could be any of 100 different fields and criteria, even down to shoe size and height.

                          What I'm thinking may be better and I would need your help with is if I did the search already and say found a result set of 100 out of 700 people who fit whatever strange criteria I need.  Then have a button that I push to constrain the previoiusly found set with the job and date information.  That way I'm not restricted in having to program in all the scenarios that may happen and it's just a constrain found set button that takes two arguments in - Job and Date and then filters the 100 down to 10 or whatever.

                          If you email me directly - dan@aprildawn.com, I can tell you more info regarding the project and perhaps we can work it out and post the final solution on the forum.  Let me know.  Thanks,

                          Dan

                          • 10. Re: Using Multiple Tables / Portals and PHP - Simple situation, I need help
                            philmodjunk

                            You can certainly use constrain found set in  a script:

                            Enter Find Mode[]
                            //use set field with global variables or constants however you need to "constrain" the found set
                            Set Error Capture [on]
                            Constrain Found Set[]

                            That's all there is to it.

                            • 11. Re: Using Multiple Tables / Portals and PHP - Simple situation, I need help
                              ChipCustomizer

                              Ok - I see the constrain found set working but here is the tricky part.

                              I basically have my pop up custom dialog box now in the following form:

                              Enter Find Mode []
                              Show Custom Dialog [......
                              Set Error Capture [on]
                              Constrain Found Set[]

                              The Custom Dialog box is asking for a Job name and a date.  I enter a job name and > 1/1/2000 for example.

                              If I query first and run this script it will restrict the results to people who HAVE worked a certain job since a certain date, I want the opposite.  I would like to run a query first, then restrict the results to a "show omitted only" of a query but I don't want to show all omitted from the original query - if you know what I mean.

                              Is there a way to do the original search (whatever criteria I need), then show any of those results that HAVE NOT worked a certain job.  The original query and then show omitted I don't think will work since it will mess up the first query, no?

                              ....Dan

                               

                              • 12. Re: Using Multiple Tables / Portals and PHP - Simple situation, I need help
                                ChipCustomizer

                                Latest update - I was able to do what I want but kind of in reverse.  Basically I have to run my script first, then add a search for what I'm looking for.  Here is what I have:

                                Show All Records
                                Enter Find Mode[]
                                Show Custom Dialog ["Restrict found person by jobs worked";"comment";job::name;job::date]
                                Perform Find[]
                                Show Omitted Only
                                Go to Layout ("Main"(Person)]
                                Enter Find Mode [Pause]
                                Constrain Found Set[]
                                Go to Layout ["List"(Person)]
                                Exit Script[]

                                This basically finds everyone, then restricts it to people who haven't worked a job since a certain day, then puts me in a find mode on the main window so I can choose additional criteria (ex - available, active, male, ...) then I click continue and it constrains the original omitted set to what I need.  Seems a bit backwards but works.

                                • 13. Re: Using Multiple Tables / Portals and PHP - Simple situation, I need help
                                  philmodjunk

                                  Good idea for doing the omit first!

                                  You can delete the Show All Records from the first line of your script, BTW and you'll still get the same results.