Stumped

[geod]

Hi, I have started on project I imagined to be not to difficult, but have

become stumped. Was wondering if anyone would be willing to share ideas on the basic relational structure that I would need to complete this project.

It is a member database with a basic table having fields for the member

info: primaryID, name, address, phone, etc.

Then I need a forms database to hold dozens of forms that can be

associated with a member who requires a particular form.

The forms associated with the member will need to be archival, that is a

permanent record. Some of the data for the forms will be pulled from the

member table, other data will be unique to form.

I would need to be able to choose from a list of forms available and then

have a portal on the member layout showing those forms associated with the

member.

Setting up the member table, building the forms and the other basics of FM

have not been a problem, but when I get to the point of setting up how to

store the forms and the archival records I am getting lost.

I have tried having the forms in one table with the permanent records or

having a table for each form with the records, and with various different

attempts to have a join file to link everything together.

Well I have to admit I have hit a wall. If anyone has any ideas I will be

eternally grateful. If I have left out any details please ask, the brain

has become so flustered it is even difficult to write about this.

Thanks, graham

[RalphL]

It sounds like each form is a table with some data the same as the member table. If a form is used more than once for a member then it might be a one to many otherwise it would be a one to one. Since this ia archival, I would use lookups rather than relational data. If each form is a table then a portal would be required for each form.

To get the names of the forms used you might be able to use the valuelistitem function to make related value lists for each form and concatenate them into one field. In each form have field that has the form name and date. Use this for valuelist.

[geod]

I have comtemplated the approaches you bring up. One of the problems I thought with each form being a table was the fact that you mentioned, that each form would have to have a portal and with dozens of forms that presented a layout situation I wanted to avoid. Unless one had a join or link file between the form tables and the member table, and that is something I have not quite figured how to do.

Also how generate a list of a of the forms in that situation puzzled me, I will have to ponder the solution you suggest. Thanks for the input.

[RalphL]

Have you considered saving the forms as a .pdf? You could put them in a container field along with other data such as a foreign key field, form name & date. Haven't done anything like this myself but you could put all the completed forms into one table.

[Fenton]

The LayoutNames function can be used to produce something that can be used for form choices, if you name the layouts according to what you'd want to see.

Check out this example file by Ilyse Kazar, http://www.datatude.net.

http://datatude.net/Demos/LayoutReference.zip

It shows how to build dynamic navigation from the names. It's v.6, but should work in 7.

Whether or not a form needs its own table would mostly depend on how many fields it shared with other form(s). They could all be linked via the MemberID to the Member table, so you should be able access any data you need.

As far as archiving forms, that shouldn't be necessary, as long as you have a date. You're already going to have many tables and layouts; no sense in doubling your work.

I don't think you can avoid "lots of layouts," as each "form" would need its own layout (that being the nature of forms). But it would not be difficult to build portals showing a member's forms, or to navigate/choose new forms. Layouts, in some sense, are just another type of data, if you use the Design functions mentioned. It's an exercise in modular thinking; well worth it when you have a lot.

One trick I learned was to group the layouts that you want to show for navigation together, in Layout Order (which matters), then "block" them in with a uniquely named dummy layout at each end. That way you can parse out your "form" layouts from the list, ignoring layouts either before or after them in the order.

[geod]

PDF's are already being used to develop some of the forms, which is well under way and yes it will take a number of forms for this. But my problem is in tying this all together, as the forms records do need to be archived as some of them are State and Federal forms which should record the current info at the time the form was issued. I understand that LayoutNames can be used and I will explore that some more. Part of my problem I think is in framing the question properly. I do appreciate the input as it helps me get a better idea of what I am asking. Thanks

[RalphL]

I was thinking that the PDF's would be stored as separate files. The image path would be stored in the database much the same as you would store pictures. Thus you would have one database table that stored all the form data.

You may need a set of blank form layouts.

P.S. My sister lives in Silver City.