Moving to new Lunch Program - need to combine and divide

[Nancy Spoolman]

I do not know if this is where I should post, but I am so lost.

I am pretty good at relational databases. I do have FM Pro 10, but have still been using 9.

I work for a school district and we are buying a new lunch program. The old lunch program I have retrieved the data from, put into Excel and brought into filemaker pro. I have used student numbers to compare to our current student information system and old lunch program so I know they match. I then use the household to put the students in using a portal.

the old lunch program tracked the money by the family. The new lunch program tracks by student. No problem, except I need to find a way, if possible, to divide the family account balance between the students. I have no idea how to go about setting up the calculation. I have each student under their proper household, but how does one count that "group" and make it divide by students in the group and divide into balance?

Then when we upload the data to the new program, each student will have money divided between them.

So: Spoolman Household has 3 kids. The Tipton Household has 2 students. The Becker Household has 5 students. How do I go about telling that household group to divide into family balance by number in group?

I have used the Unique and Duplicate counting previously, but this one is beyond me.

I hope someone has a simple explanation for me.

Thanks!

Nancy S.

[efen]

I have assumed that you have two tables - family group and family members. Does the attached help?

... and welcome to the forum

lunch.zip

Edited March 3, 2010 by Guest
welcome added

[Nancy Spoolman]

Yes, I have a list of students with household name from our Student Information System. Then I have the Family balances attached to Family household name table.

Now, just to get my head around your AWESOME tables! I checked out your layout to see how you configured it. Once I get it figured out, it should be simple? Ha! You make it look so easy.

The one thing I have never done is combine all the tables into one file. I just keep the relational files in their own folders. I have sometimes up to 8 files to relate to one main database. I know that pretty well and can get anything pulled together for our district.

Someday I hope to take a class on FM Pro. I know I could really cut some corners.

Thanks for your help. I hope to get it done tonight.

Nanc

[Nancy Spoolman]

javascript:void(0)

Hi Efen! Your example was absolutely wonderful! I didn't quite understand the pk and fk description and tying it together, but I studied it and matched it to my two relational files as best I could, added what you had and at first I got the ? marks in the area where the money divided should have been. So, I went back and studied your template again and then remembered the relationship table was where I had seen the pk and fk tie-in. I had to make another relation for that, as my first was to tie the students with Family ID and student number since the student number is what we have to use to tie to our Campus (Student Information Database). Anyway, I danced for joy when the amount came into the portal and I was able to export fine. I still have to figure out how to tell one student to have 12.13 and the other 12.12, but will work on that tomorrow.

For now, it's all set up and scripted, ready for next month when we turn everything over to the new program. I think when all is said and done, to prepare for it it should not take longer than 20 minutes to retrieve the data from the old program, export it from the PC and run my script. Exciting!

Thank you! Thank you!

Nancy S.

[Nancy Spoolman]

In previous posts I mentioned we were moving to a new lunch program. I have one last issue I need to resolve before I am finished with the scripting, comparing the two databases. The secretary of Food Services has to enter the benefit type meal status when parents fill out a form. That changes several times over during the year, for many. She also has to enter the data in our state database. What that does is create several records for the same student. I am pretty confident that when I run a duplicate count, I am getting the accurate result from the state.

However, there are a couple of things I need to do to match what is exported in the old lunch program, against the state DB. We will be using exports form our student information system (Campus) to keep the new lunch program updated. This is also tied into the State system.

What I need to do is match the benefit type field from the old lunch program to the benefit type in the Student Information System and find any discrepancies. I want to script it, hopefully, so it will export an excel file showing those that do not match.

I'm just not sure how to start the process. I have related both templates using the student ID and have been eye-balling the two fields, but there must be a way to automate this.

Any help would be appreciated.

Nancd