3 Replies Latest reply on Dec 21, 2015 6:07 AM by mikebeargie

    Check availability before saving?

    Timbalord

      Hello together,

       

      im really new to filemaker but i have some experience with SQL. Filemaker seems to use different solutions to SQL. For that, here is my Problem:

       

      I have 4 tables

       

      Employees

      ——————

      employee_id

      name

      adress

      and so on

       

       

      Projects

      ———

      project_id

      name

       

       

      EmPros (jointable Employees and Projects)

      ————

      empro_id

      employee_id

      project_id

      empid_projid (a formal based combination of the employee_id and the project_id to make this combination unique)

      status (active or inactive)

       

      Projectstatistics

      —————

      empro_id

      date

      earnedMoney

      hours

      and so on

       

      And here is my Problem:

      If the employee has to left the project, i dont want to delete this in the EmPros table, because of he can enter the project after a few days again and because of the Projectstatistic. So i made the field status in the EmPros table and set this to inactive per script. That works fine.

       

      But when i set the same Employee again to the project i get the message, that the employee is still with the combination in the tabel (that is correct, because i made the combination as a unique field). I have to be able, to check if the combination is in the table and when its in the table turn status from inactive to active.

       

      I dont find a solution, that let me do a query like this in a script.

        • 1. Re: Check availability before saving?
          mikebeargie

          you can query the existence of that join record a few different ways via script:

           

          1) Go to the join table, perform a find, if there is no find error, then the record exists.

          Enter Find Mode [no pause]

          Go To Layout [ EmPros ]

          Set Field [ EmPros::status ; "Inactive" ]

          Set Field [ EmPros::Employee ; your employee ]

          Set Field [ EmPros::Project ; your project ]

          Set Error Capture [ on ]

          Perform Find

          If [ get(foundcount) ]

             You have a record and do stuff here

          Else

             There was no record, so create one here

          End If

          Go To Layout [ original layout ]

          2) Check for the record via an ExecuteSQL() calc:

          Set Variable [ $count ; ExecuteSQL("SELECT COUNT(*) FROM EmPros WHERE status = ? AND employee = ? AND project = ?" ;"";""; "Inactive" ; your employee ; your project ) ]

          If [ $count ]

             You have a record and do stuff here

          Else

             There was no record, so create one here

          End If

           

          That should be enough to get started, there's probably at least two other methods for checking for the record.

          1 of 1 people found this helpful
          • 2. Re: Check availability before saving?
            Timbalord

            oh jesus, so easy and i didnt see that. thank you so much.

            • 3. Re: Check availability before saving?
              mikebeargie

              It's what we're here for! Having come over from PHP/MySQL myself, it's amazing how simple things in FileMaker can be vs. canned scripts/procedures.

               

              The ExecuteSQL() function was a more recent (v12) addition, but very welcome indeed.