4 Replies Latest reply on Aug 12, 2016 7:28 AM by philmodjunk

    SQL

    DylanPaschall

      I made a Database to use for a check in/out system with some of my audio equipment and want to find a way to automatically calculate the amount of empty fields in the "Date Checked In" Column in my "list" Layout so that I know how many units are still in use and wan to insert that count into the "Currently Checked Out" Field on my "script Table" layout. I attached the Manage Database window to show what the fields are and what kind of field they are. I also attached a Picture of my "Script Table" Layout and my "List" Layout which is where I want to get the Calculations from.

       

      When someone goes to check the unit back in, I type the serial number in into a find request and it automatically timestamps the field on the most recent record which marks it as "checked In"

       

      There currently is 1 unit that is still out because there is nothing in the Date Checked in field.

        • 1. Re: SQL
          philmodjunk

          You have used SQL as the discussion title, but I don't see any particular need to use it for the issue at hand.

           

          The first problem that I see is that you seem to have one table where you need two.

           

          I looks to me, based on limited information, that you need at least two tables: Equipment, where you have one record for each item of equipment that can be checked out. and Log--a table where each record records the loan of a single item of equipment with a date checked out and a date returned. The same item checked out and returned an indefinite number of times.

           

          A count of items currently "out" would be a count of all the Log records that have a checked out field but no checked in. A count of items currently "in" would be a count of the equipment records where their "last" log record has data in the "checked in" field.

          • 2. Re: SQL
            bigtom

            Im with Phil on this. Not a lot to go on, but assuming you have a Log table maybe something like this should work using SQL.

             

            "SELECT COUNT(id) FROM Log WHERE CheckOut IS NOT NULL AND CheckIn IS NULL"

            • 3. Re: SQL
              DylanPaschall

              I got the "equipment Inventory" table set up with the serial number of the item, the model, date checked out, and date checked in. I am not to much of an advanced user in FileMaker, so how would I get the fields to automatically import the data from the "log" table into the Inventory table when it fields are entered on the "Log" table?

               

              And then what would you recommend me use as a script to run a count to show what is checked out on my main view and what is not checked out. I haven't gotten a lot of time using scripts and am not very good with writing script.

               

              Thanks for all of your help in pointing me in the right direction.

              • 4. Re: SQL
                philmodjunk

                DylanPaschall wrote:

                 

                so how would I get the fields to automatically import the data from the "log" table into the Inventory table when it fields are entered on the "Log" table?

                You wouldn't import any such thing. There will be many entries in the log table for any given record in the inventory table. That's why you need two tables in the first place. What you can do is put a portal to Log on the Inventory table to show all the log entries for that Inventory item. You could also just show fields from the most recent log entry for a given inventory item as a way to show whether a given item is checked out or in. This last option can be done with a one row portal if you sort the portal (or the relationship) by the date checked out field in descending order.