weekly progress form.

[adamkarwowski]

good morning everyone and happy new year. this is my first post (WOOT!) and probably not the last. lol

lets say i have 12 forms and one is a weekly progress report. how would i make a new one for the same person but not having to make a totally new record. i guess what i am trying to say is i don't want 5 records with the first one having all 12 forms filled out and the other four with only the weekly progress form filled out and the rest blank.

thanks in advance guys

[Steve T.]

Welcome, Adam and HNY! I think I was online when you were signing up New Year's Eve.

As to your Q, I'm not sure I follow exactly but my best guess would be to suggest a relational database. If you're new to relational db's (like me) it may take a bit of studying to understand how they work but it could be your answer.

You would have a db for your people, another for your basic set of reports, and a 3rd for your weekly progress forms. You would need to create a new record for any db in which you wanted to do a form, but you wouldn't have to make blank forms as part of any pre-made set.

Hope this helps! There're experts in FMForums (usually with thousands of posts and lots of stars) that may be able to help you more, but I hope this gives you a headstart, newbie! Again, welcome.

[adamkarwowski]

thanks for replying sktajiri. i see your a general computer guy like me. i thought about setting up relational db but i was kinda hoping to keep it all in one db. i was kinda hoping someone could show me how to make a script or something that would not replicate forms when doing a progress report for a record you already have. oh and happy new year to you too.

you wouldn't play any online games would you? lol

[adamkarwowski]

anyone else?

bump

[andygaunt]

I think we are just a little confused.

If you dont want to create a new record for the person, yet you want a new progress report.

You could keep creating new layouts I guess. But, the best bet is to have your progress reports in a related database. This way, you can show the related records and it will only show the reports that exist. It will not duplicate the data as it is related to the contact, and you won't have all the layouts.

Is this any easier? Do you need help on setting up relational db. Why are you hesitant?. Bet you didnt expect to answer more questions than you post huh!!!

Let us know

[adamkarwowski]

right now i have one database containing 12 layouts.

and those layouts are forms for example one layout would be a client intake form. another would be a life skills checklist. one of the layouts is a weekly progress report. i have some feilds shared so i wouldn't have to enter he name in all the layouts when i enter a new record among other feilds. on the weekly progress report (after the initial one in the record) i need to hit new record in order to enter a new weekly progress report for a record that already exists. but i do not want a new record. i just want to add a weekly progress report to the record itself that already contains a weekly progress report from the week before.

i hope i explained it a little bit more clearly.

if you want to take a look at it i can send it to you. its only half a meg.

[adamkarwowski]

oh and i forgot to mention that the way that the layouts were done was so that they can be printed to use as blank paper forms.

[Guest]

adam.... i would break out the weekly progress reports into a separate database. move the report fields to the new database and duplicate the field that uniquely identifies each client. create a relationship between the main clients database and the weekly reports database. create a script that finds all reports related to a single client and trigger this script with a button on the main client database. use fmp to enforce the uniqueness of the client id field and write a script that eliminates duplicate records in the main client database.

[adamkarwowski]

whow thats alot of steps lol. i will definetly try to do this tomorow.

i hope you dont mind if i ask you in the future to walk me thru one or two steps you mentioned.

thank you for replying it really helped alot.

[Guest]

adam... OK. Glad to help if you get stuck.

The first step is to define a field that uniquely identifies each client. SSN is always a popular choice. An alternative is a autonumber type of field (IDENTITY in MS SQL7) that increases by one for each new client. You can tell fmp to validate that the field is unique (does not exist already in the database) so that only one record can exist with a given value in the "unique" field. This is the so called primary key. Unfortunately, the "uniqueness" can be overridden, thus the eventual need for a script that can eliminate records with a duplicate values in the "unique" field (umm database maintenance). If you use SSN you must also enforce any pattern eg: ###-##-####.

The second step is to determine what is a repeating field in your database and break this out into a second database table. In your case, the obvious candidate is the weekly report.

I have gobs of books on relational database design, but for a beginner i suggest "Database Design For Mere Mortals" by M. Hernandez.

[adamkarwowski]

ok so i guess step one would be to set up the rational databse with only the weekly progress report in it right?

[Guest]

adam..... yup just create a separate database with weekly reports. if you have a field that uniquely defines a client such as SSN just duplicate this one field in the main database _and_ in the weekly reports database. you can use this field to "link" records in the main client database with records in the weekly report database.

once you have created the two "flat files", tell fmp to create a relationship between the two files using the ssn field. you can then use scripting commands from the main database to find all "related records" in the reports database. so it works like this. i search for a client in the main database and then i trigger a script which takes me to all weekly reports related to that client, sorted by date descending (so that the latest report is topmost). i would even have a script in the reports database that takes me back to the single related record in the main clients database. thats it!

to make even easier i add scripts to the main client database that creates a new related record in the weekly reports database and fills in the ssn automatically.

finally add buttons that trigger the scripts.

Have fun.