lorivol

LaRetta, I appreciate this input so much! (I also appreciate your signature line!). I am totally self-taught on filemaker, and while I do have some things I'm really proud of figuring out, there are still many things I don't have a complete grasp on. If you have time could you elaborate more on a couple of your recommendations? 1. Regarding the Reviewer tables... are you saying that I could have multiple reviewers but use the same table? If I did that, would I have to make a field with the reviewer's name? (Not all the reviewers use fm, so some of them fill out their reviews of applicants in excel and I import it for them, so it couldn't be an autoupdate field by user). 2. I've been looking for more information on the "1" and "0" issue, as well. This might seem really basic, but if the other people using the database (we have it on fm server), need to be able to click "yes" or "no" to answer certain questions, like whether or not a student returned their contract. I don't think it would be best to instruct them to type in 1 or 0, but is there a way that they could enter the text and then it is converted in the same field to 1/0 for calculations, but still reads "yes"/"no" when you look at it on the layout? 3. I like the idea of creating a temp file, because I think it would help out with the 1:1 issue as well as the field names. If I had a temp file with ONLY the fields that are on the excel spreadsheets I receive, then I could import the spreadsheets much easier and then have the import script to take that into the overall database. Another thing that is pretty basic, I'm sure, but I haven't tried an import script before. I'm not sure I understand where I'd put that or how to write it. Do you know of any reliable tutorial on import scripts, specifically? Thanks again, I hope you have a good weekend!

Well, I guess that would help out if you could see it Attached is the file. Use the login name Admin2, with no password. Thanks! Example Admin2.fp7.zip

It works! Thanks so much!

I truly appreciate your thoughts. As you'll be able to tell from the zip file, I work at a university, so I had to remove all the records from the database for privacy reasons. I also deleted all the report layouts, mostly because there are a ton of them and some are used, some are not, and I thought it would be easier to not confuse things with layouts that might be outdated. You have to log in using "guest." The layout is the main layout I use for each student, and you can see the number of fields and the tables I have so far. You might ask why some tables are related by student ID and some by applicant ID, which is an auto-enter field. There is no answer to that except that this is a definite work in progress. I also don't know much about the join relationships you discussed, so I'm uncertain as to whether or not I may need to use them. The other issue I face with fields in separate tables is that often I import a new group of students. Then, based on their qualifications, I might want to mark them all with a certain status or use some other field that describes them all. If that field in the same table as the rest of the student's info, I can simple do a replace field contents. However, if it is in a separate table, is there an easy way to create a new record in advance so that the replace field contents would work on a related field? For example if table 1 is the main table where lists are imported and the auto serial number is created, could the record and matching serial number be simultaneously created in all the related tables so that if the field from a related table is on a layout, it would act the same way as if they were in the same table? ( I know that is probably really confusing, but I am not sure how to explain it better). Again, thanks for your help! Example.fp7.zip

Yes.

I have a database that keeps growing larger and larger (the number of fields, not the number of records). I want to reorganize before things get out of hand, but I don't know if I should create related tables for different categories of information or if there is a better solution. The main content of my records comes from an outside source, which sends me an excel sheet with applicants for the program where I work. This is all demographic information and contact information. Next, I begin analyzing applicants, and marking the applicants in various ways, creating new fields based on their qualities, status, etc. The issue is that it is becoming increasingly difficult to import the excel sheet when I receive it (on a weekly basis), because of all the fields I've added. I also have to admit that I haven't quite mastered the recurring import function, yet, but mainly because the person who sends me this file never uses the same headers, so I've been afraid to try this. Basically, it takes way too long for me to match up these records every time I want to import new records, and update the existing records, and I was thinking that I could have only the items on the excel sheet in the main table, and then have all my added fields in related tables matched by an auto-created serial number. Additionally, this would make it easier to locate fields if they are categorized in separate tables based on their specific use. I suppose the question is: Is it stupid to make related tables for the sake of field organization? If so, how can I better organize these fields to prepare for import?

Hello! Just one program in this instance. Accepted and denied applicants are in a field where they are labeled as "yes" or "no." Currently every applicant has been set to yes or no, but during the acceptance process they are left blank until we make a decision. Please let me know if you need more information. Thanks!

I have a project where I'd like to create chart layouts that have multiple series. I know I need to add items to the y-axis and write a calculation, but I can't figure out how to write the calculation. Here is the situation: I have a group of applicants to a program (all applicants) Within this, I can search for the accepted applicants (accepted) and the applicants who were denied (denied) There is other information about each applicant in all of their records, for example, the state they're from. I want a bar chart where the x-axis is count of people and y-axis is their state of residence. I can do this. The tricky part is adding more series to the chart. I want to end up with the count of all applicants by state, all accepted by state, and all denied by state, I just can't figure out how to write a calculation that will pull up only the accepted, and only the denied applicants. I've attached an example excel chart with what I'm looking for (where A, B, and C would be the states of the applicants in this example). Any help is much appreciated!

bcooney, [color:red]Do you always reimburse the entire expense amount? Yes [color:red]Do you always reimburse a submitted expense? As long as the student is submitting receipts for expenses related to their research project, we'd be authorizing the scholarship for the full amount. If someone gave me a receipt for pizza, for example, I would not enter it into my system (and I'd send them an angry email about misappropriation of funds : [color:red]How do you submit expenses (at least what you designated as "Amount to Reimburse?" Do you need a form (with an ID) for each Student or can you submit a report that includes all expenses that have not been submitted for reimbursement, sorted by Student? Yes, I'd rather do the full list of all students who have turned in their receipts for reimbursement at one time. The Scholarship Authorization form layout is what is required to reimburse the students. In the previous example, it just shows one student, but it is set up to be used in the "list view" where it would show multiple students. I would want it sorted by student, so that each student had only one each time I submit the authorization form. See attached. [color:red]When they do pay, what do they reference? The expenseID? or just the StudentID? Or both? The scholarships office is on an entirely different system (filemaker is simply what I use in my office to keep up with things), so any expense IDs or numbers that I make up would be purely for my own record keeping/tracking purposes. The scholarships office uses the student ID, which is a number generated by the university. That field is set to merge into my scholarship authorization layout because the honors student table is related to the HSP Research Funds table. [color:red]Finally, what happens if their submitted expenses exceed their Scholarship? To speak to your last question... I hadn't previously thought of that, but If the student gets to the end of their scholarship and have $20 left of their $5500, but submitted a receipt for $50, I would just enter in $20 to filemaker and make a note on the receipt that $30 was not covered by the scholarship. I think that would be the easiest thing to do.

Trying to clarify: In this situation expenses or purchases and disbursements are all debits from the original $5500 beginning amount. The student has $5500. He purchases a plane ticket (out of pocket) for $300. He confirms a hotel for $100 (out of pocket). He turns in those receipts to me. I need to keep up with A. What he has spent his money on (purchases) B. How much of his money has been spent (summary field) I also have to fill out the disbursement form so that he is reimbursed for the $400 he just spent. Since he turned in those two purchases, I would reimburse them both at the same time (by using the scholarship authorization layout) instead of doing two separate authorization (disbursement) forms. A month later, the same student might turn in another receipt for $30 in lab supplies from an online supplier, $50 in lab supplies from a local store, and $100 in expenses from the bookstore. (All paid for out of pocket by the student). Now I would have to be able to put all those new purchases into the database and create a second scholarship authorization (or disbursement #2) for $180. Now, that students total scholarship should reflect that he only has $5020 of his original $5500 to spend.

Besides being able to summarize all the scholarships, it is equally important that I am able to take groups of the purchases and total those up as a separate disbursement. Attached is the scholarship authorization form, which I am required to use. I recreated the form in Filemaker and would want that total of purchases to be the number in the total stipend and the disbursements fields. The form also pulls out the student information (name,address,ID) from the honors student database table. This is what is causing me the issue. I can make the purchases add up, but then I couldn't figure out a way to only pull out a certain group of purchases (which would be new purchases) and add those together for this disbursement form. Please let me know if I failed to answer any questions! And thanks again to everyone!

Hi everyone... sorry for the confusion, by the different levels, I just meant that the students can be continuously using it so it's a different amount for each person at any given time, but they all start with $5500. (We do have another program where the students are awarded different amounts, but I'm not even going to get into that one right now!). To try to answer the questions: The way I have it set up is that a disbursement can contain multiple purchases. I was just grouping them in this way because I need to be able to pull out the groups (disbursements) into the scholarship authorization layout. However, when I tried this using just one table for disbursements/purchases, I couldn't get the summary field to only total the purchases for one disbursement instead of the whole group. I'm sorry... as I'm typing I know it's very confusing, and I'm likely using terms that make it even harder to see, so I've taken some screen shots. One is just showing the table relationships (the only ones I'm using for the current issue are the honors student files table and the HSP research funds table. The other is the layout where I plan to enter in the students purchases as they make them. So each line would be a unique purchase, i.e. plane ticket for conference $300. I suppose the question is whether I need to make a new table for disbursements and add this purchases portal into a layout with that as the "show records from" table. Then, though, what type of calc would I use to tally up all the expenditures per student?

Thank you for responding! There is only one scholarship awarded to the student ($5,500). However, the student is awarded the scholarship in his/her first-year, but likely will not begin spending the money until his/her third or fourth year. Once the students start spending the money,they probably won't use the entire $5,500 at one time. Therefore, they will have multiple purchases. My original idea is to use a field I called "Disbursement Number" to sort the list for the scholarship authorizations (a separate layout), which are basically reimbursement requests for money that the student has spent on their research project. So, the student who is going to a conference has Disbursement 1, which consists of a plane ticket and hotel reservation. What you're suggesting that instead of "disbursement #" being a field, that it should be a table and then have purchases as portal within that table? That does make sense to me. Would I still, though be able to do a summary field or a calculation and add up all the totals of all the disbursement numbers for a certain student (to ensure that they don't go over their $5,500 limit)? I don't think I'd need to have the third level of separate items within each purchase because I could then make each individual item the "purchase." Student-> Disbursement-> Purchases Currently I do not have a scholarship ID set up. Also, to answer your question, there would not be a purchase split between 2 scholarships. Again, thank you for your help with this. Please let me know if I should proceed by adding a new "Disbursement" table. The most important thing being if I did that, would I be able to summarize: 1. The total amount of money spent per student. 2. the total amount of purchases made per disbursement (to use with my scholarship authorization layout). Many Thanks!

I work at a university and have a database with student information. Some of the students have different levels of scholarship awards that they use at different times in their collegiate career. The student information (name, demographic info, major, etc.) is in one table. I created a new table for the scholarship awards and linked it through the student's serial number in my database. All the award winners start out with an award amount ($5,500) that they can use whenever they are ready to work on their projects. I created a new layout with the student's information coming from the first table, and then a portal with fields for the project money. The way that it is set up now, I can total all the awards for an individual student, but can't figure out how to pull out certain awards as the student uses his/her money. In order to disburse the awards, I have to send a scholarship authorization to the financial aid office, which I have recreated in another layout. I need to be able to enter in the student's receipts for purchases made toward their projects, and then click over to the disbursement layout and have only certain entries show up. there could be more than one entry per disbursement. For example: Student A is going to an Engineering Conference and has already booked a flight and paid a deposit for a hotel. In the portal for funds to disburse, I've entered: 348.80 - Plane Ticket to Conference 88.48 - Hotel Deposit I need to add these two amounts and make them appear on the scholarship disbursement layout as a total amount of disbursement for each student. The the students work toward spending their 5500, I assume this will become more complicated because I'll need the disbursement to only pull in the NEW expenses, and not reimburse for previous expenditures. Ex. the same student when he returns from the conference will have receipts to turn in for meals and the rest of his hotel. I will add those in and need to reissue another scholarship authorization ONLY for those new amounts. Additionally, though, I need to be able to have a running total of all the expenses so that no one goes over their 5500 limit. I know this is very long, but I tried to be as detailed as possible. Any suggestions/help is much appreciated!