"and also imported the individual Excel sheets with the information of which of the clients came that week as separate tables."
The Weekly Attendance records should all be in the same table, with a field showing the date.
If the attendance is in one table then you can generate a sub-summary (by client) or by date and create summary fields which count the number of times they have attended.
I would import one Excel sheet then place your cursor in the date field and type the date of the Excel spreadsheet then select Records > Replace Field Contents and first option is replace with 'same date' so just say OK. After each import, set the freshly imported found set with the week's date.
This can be easily scripted. You can use a global date field to hold your weekly date, enter the date then script would be:
Replace Field Contents[ by calculation; globalDate ]
Melinda, pull the client's names into your Attendance table as well and here is why. You need a relationship between the two tables.
Originally, you will need to use name but you should quickly change to unique IDs. I have just answered the exact question here:
"I would like each client to list the date that he or she came to get food, but would settle for a count of how often they used us -"
Donations table in this thread would be same as your Attendance table. If they are related, you can view a client and see (in a portal) how many times (and when) they have shown up. You can also then count them.
I'm trying to follow what you wrote in your last post - I clicked on the link but somehow I couldn't get the same end result you did. Perhaps because I couldn't follow your directions toward the end:
"4) Place cursor in Donations::ContactID field and select Records > Replace Field Contents. Specify 'Replace with Calculated Result' and in the calculaton box, find and double-click your ContactID from your Contacts table to insert it into the calc box. Click OK and then REPLACE."
I can't figure out how to select "records". I have FileMaker 11 -- maybe the menus changed wording? I don't know if that's a crucial step or not, but I did link the client numbers in the relationship chart. Then I tried to insert the date served from one table into the date served field in my client table, and nothing happened. I am so frustrated -- I just don't know what I'm doing wrong!!!!
Okay -- what a feeling of accomplishment -- I tried it all over again and IT WORKED!! I am so excited. So right now I have a separate table for each time we give out food. I linked the tables by the client number and am able to have the date come onto the client card each time they visit our Pantry. That's great -- it's just what I wanted. Only it's fairly labor intensive to create a new table each time, where I'm re-entering all the fields and formulas, especially given that I want to go back and create a history of the last few years. There must be an easier way -- any idea?
Also each time we are open we have new clients -- should I just manually enter in their information? It will be on the Excel spreadsheet for that week each time I import it, but I don't know how to transfer those names automatically to my central client records.
Similarly, if a client's record changes (ie: a family has a child or someone dies), do I need to manually update the record, or is there a way to do that automatically (the new information will be on the most recent Excel sheet I am importing each time).
Anyway, I can't thank you enough -- I am so thrilled that you helped me figure out this very crucial step!!!
All of your questions are really business decisions and I can't make those for you, Melinda. But it would be best to replace the Excel spreadsheets and enter directly into FileMaker. In this way, when someone comes in, you search for their name (enter part and find result), update their information (ask where they are currently living or any other questions you might ask that they might share) and update their client record. Maybe get their birthday and next time they come in, give them a cup cake with a candle for instance ...
If all done in FM there is nothing to manipulate or enter. If they don't exist in the table, add them on the spot. You can have a button on their client layout which simply says, "attended today" which can (in a flash and behind the scenes) insert a new attendance record which simply logs their clientID and date attended into the Attendance table or it could open an attendance record if there is more infomation about their particular stay that you want to track.
Your client table can even hold things such as if they have family for emergency notifications or so if they come in, you can just connect with them personally because of something in their client record. Using FM directly is the best answer. If you add new people to Excel, you won't know if they are new or return visitor; you won't have access to this wealth of data in FM about them.
"So right now I have a separate table for each time we give out food."
You should probably have a Distribution table with unique DistributionID. It would be date, maybe menu of food served, or whatever. All should be in ONE table. Split anything which might be multiple into different table. For instance, menu might be several items. So you could have a menus table with MenuID (unique) which holds DistributionID and each menu item served is a different record.
As you begin seeing the power of relational design (splitting the 'many' from the 'one'), it will begin to come together quickly for you! And it is much faster and easier to type a few letters and retrieve a client's information in FM and much easier to enter their stats, keep track of alergies to foods, tell them of next week's menu, list their favorites, track if they have nightmares so a staff can check on them ...
Time to move to database. Data entry can be simplied; that's what we do. :^)