Are all the "boxes" in your screen shot separate tables--each has a separate row on the tables tab or do some of these Tutorial: What are Table Occurrences? share the same data source table? If some have the same source table, which ones?
Why do you need separate table occurrences for signing items in and out? Can't one table occurence with the same layout be used for both?
I actually was evaluating that myself and I'm going to change it around a bit. The bottem "box" intitled "Transactions_Line_Items_In" is just an occurrences that shares data from from one table, and the bottem "box" intitled "Transactions_Sign_In" utilized a completely seperate table. I was thing of Merging my "Transactions_Sign_In" and "Transactions_Sign_Out" tables into just one table (this way I can have each User specify the 'type' of transaction they are doing in one table), and only have one occurance of "Transaction_Line_Items".
What are your thoughts?
Well that's why I asked the question. I'd use a single table for signing stuff in and out. You can have one date, time or timestamp field for when it is signed out and a second such field (or set of fields for signing the item back in.
I don't think you'll need a separate table nor an additional table occurrence to make that happen.
PS. check your starter solutions. FileMaker 11 has a lending library starter solution you may want to look at for ideas. And FileMaker 12 has an Assets starter solution, that I think has similar features.
Thank you again for the advice! I utilized what you told me as-well-as some other items I learned about and I was finally successful in what I was trying to accomplish. Now I have another piece of the puzzle that perhaps you might be able to help me out with.
As each UserSigns-Out a piece of Equipment, I would like a conditional logic statement to check to see if the piece of Equipment has a status of "Signed-In", or "Signed-Out". If it IS "Signed-In", then they can proceed to sign it out (aka make a Sign-Out record) and change the piece of Equipment's status to "Signed-Out" (basically prevent Users from being about to Sign-OutEquipment if it is already Signed-Out). And the I would like to apply the same logic but in reverse for Signing-In Equipment.
Below I have provided a screenshot of my database to veiw it's structure:
You still have stuff here that I don't think that you need.
First, I suggest a change in the match fields used:
Transactions::__pkTransactionsID = Transactions_line_Items::_fkTransactionsID
It may seem unlikely, but unless you only a single user database, it's possible that two users might just start signing out/in equipment at the exact same second in time and then you have a problem easily avoided if you use a serial number field in Transactions instead. You can remove the Date Time Out field from the line items table. Any time you need that data from the context of the line items table, you can refer to the time out field in transactions.
A portal to line items placed on the transactions layout can also be set to filter out all checked in items so that only remaining items still "out" appear in the portal and this does not require adding another relationship to do so.
From what I see here, each item has a unique asset number, correct? If so, I can see two ways to determine if an item is "in" or "out":
Script triggers on fields in the line items portal can be used to change a status field in Equipment to mark it as "in" or "out".If you use this method, be careful to not permit deleting portal row records from the line items portal except through a script that first updates the status field in Equipment before deleting the record.
Or you can set up a relationship between equipment and Transactions_Line_Items that only matches if the Date_Time_In field is empty. Define a calculation field, cOutFlag in Transactions_Line_Items as IsEmpty ( Date_TIme_In ). Be sure to clear the "Do not evaluate if all referenced fields are empty" check box. Define constOne in Equipment as a calculation with just the number 1 and a result type as number.
Then you can define this relationship:
equipment::Equipment_Asset_Numberpk = Transactions_Line_Items|Out::Equipment_Asset_Numberfk AND
equipemtn::constOne = Transactions_Line_Items|Out::cOutFlag
Then from the context of Transactions_Line_Items, Transactions_Line_Items|Out::Equipment_Asset_Numberfk will be empty if the item is currently checked in.