Thank you for your reply. Your script does the job perfectly. I now see that I just have 1 giant table that could be broken down. Do I just create the tables then import from my existing one?
I now see that I just have 1 giant table that could be broken down. Do I just create the tables then import from my existing one?
Well yes and no.
You should at least have a "People" table (ID, Name, mail etc) and a "Assignments" table (Assignment ID, date, ForeignKeyInventory, ForeignKeyPeople). You can import people data into people, but then you will have to delete the duplicates.
Is it possible to edit the script to contain more than one field as the identifier in the email so say the ID num, the model etc? I tried using a calculation field but didnt work.
Of course, just change line 7, setting $CurrentAssignedItems from
ExecuteSQL("SELECT Item FROM ASSETS WHERE \"Assigned to\" = ?";"";"";$currentName)
ExecuteSQL("SELECT \"ASSET ID MATCH FIELD\", Item, Model FROM ASSETS WHERE \"Assigned to\" = ?"; CHAR(9) ;""; $currentName)
Apoligies as I am not being specific enough, I'd like it to list item model category and serial number for each tool. If i put all in it returns ? do i need to change something else, the char(9) im not 100% on how that part works.
The char(9) is the tabulator char, so that the list you get will have its row elements be separated by tabs.
The field names of the fields you want must be inserted between SELECT and FROM.
They have to be separated by commas.
Should a field name contain spaces, its entire name must be enclosed in \" \", like I did for the ASSET ID MATCH FIELD field.
If you get a ? you made some error.
Ok thanks, ill give it a go
Check out the database design class on Lynda.com FOR FILEMAKER:
FileMaker encourages you to "jump right in", but that's usually a mistake without some careful planning of your "foundation" (database).
If your database isn't well constructed, you'll be spinning your wheels trying to get usually easy or trivial things to work when you're using a well-defined database.
Normalization is splitting up a single huge table into related tables. This separation avoids redundancy and other problems.
There are other resources as well. Invest some time in learning this stuff. Database design is a generic skill that is EXTREMELY valuable whether you're doing FileMaker or some other relational database system
Understanding database design is a "step zero" kind of skill.
(not that we all aren't trying to get better at it all the time.)
This is out of the blue so apologies. I have now split my table to into several tables.
The SQL script runs but returns ben b, your allocated items are, ?
I have removed the \" \" where necessary and replaced table and field names, what am I missing?
Thanks in advance.
SQL Code.docx 283.6 K
FROM Tools WHERE Staff::Name
Ok thanks, seems the problem is now the ID field used in lines 10 & 11?,
10. SELECT whatID(tools i guess?), DIX_Num, Model, Category, Serial_Number FROM Tools WHERE Name.......
11. Set field ( whatID?)
In the old version it was just the 1 big table which had the 1 ID, 10. select pk_id.... 11. set field (pk_id)
Tried various ID's but still returns ?
I do see a go to original layout on line 20, but I don't see a go to layout (signOut) which is necessary for the line 11 Set Field to work. Add such a script line, preferably outside the loop , let's say after line 5.