A table for each form in solution?

[dmontano]

Every time I feel like I am progressing and building momentum, I literally hit a wall... oh how I envy the gifted developer.

 

I have a vendors database I am rebuilding. While rebuilding it I discovered that my initial build was cumbersome and flawed. I only had ONE table that was tracking both the "vendors" as an entity AND the "employees" of the vendors. If you can imagine I had multiple field entries to accomodate multiple "employees" of just one vendor. So, I created two tables: vendor_companies, and vendor_employees.

 

However, I am stumped again. Here's the situation:

 

I have a half dozon forms in this DB as well, such as a NDA (Non Disclosure Agreement) form that Vendors are required to complete in order to be a vendor of ours. I also have letters that accompany these forms. Should I create a separate table for the NDA forms? Separate tables for the other forms?

 

Is there a simple rule I can remind myself of in the future when I am building forms in my solution, such as: A Table shoud be built for each form so I can treat it as a separate entity?

 

This is important as I see my solutions getting the most value by creating forms to facilitate all sorts of processes?

 

Any help is always appreciated.

 

Thanks,

David

[doughemi]

I would have a table of forms. Each form might have its own layout, and maybe fields that are unique to it, but it fits the model I use:

 

I always ask myself, "What generic groups of things with similar characteristics do I need to keep track of?" In this example, there are vendors, vendor_employees, and forms. So each generic group needs its own table. Each generic group has its own characteristics (fields), and its own collection of members (records). Each may refer to characteristics of other generic groups (relationships and related records).

 

Some may argue that forms do not necessarily share the same characteristics, but my counter to that is "I always know exactly where to find any form I need: in the forms table!"

 

Once you (and I!) become more sophisticated in matters Filemaker, we will outgrow this very simplistic model, but in the meantime, I find it very useful to go through this exercise with each new solution, and each time I have to add new functionality to a solution.

 

--Doug

[dmontano]

Thanks Doug,

 

Your logic supports what I am beginning to see more and more. I am finding my older "solutions" being somewhat duct-taped together until I look at them again with slightly more experienced eyes (emphasis on "slightly"). It makes perfect sense to me what you have said.

 

I want to be able to find each NDA, for example, or each Vendor Review Pack that is assembled and sent out. Since not every vendor (like Staples) would ever get an NDA, it seemed quite lame for me to have a blank NDA form in the Vendors Database that was associated with that vendor.

 

Looking at it know, it is just like an invoice - a separate entity. I really struggle with seeing the forest through the trees - or is it the trees through the forest?

 

Much appreciated.

[doughemi]

Now if you have a FormName field in your forms table, you can make a valuelist that could be used in the vendor table for a checkbox field. The checkbox field will drive a script to print the form, as well as give you a central place to see which forms were submitted to which vendor.

 

--Doug

"I love it when a plan comes together" --Hannibal Smith, "The A Team"

[dmontano]

Hi Doug,

 

I am still in my rebuild of my Vendors database and have really struggled with some area - I won't bore you with that. However, I am reviewing some of the many threads I have posted so I can work on something while I am waiting for some help on the other items.

 

Regarding forms again, for clarification, you are putting ALL of your forms in one table? Why am I thinking it should be 1 table per form? I have found that my understanding of certain things was better a few weeks ago - could be the lack of sleep or being overwhelmed.

 

In any event, I would think that a form, like, let's say a "Vendor Request For Information" (which is submitted to a Vendor, they then complete the required fields on the form so I can harvest their information and then - populate the Database when it is returned to me.)

 

There would be up to forty fields on one form, other forms may be more fields and on a completely different "Topic".

 

If I build 1 table, forms, then obviously I separate them via a drop-down field as you mentioned, I guess that would separate the "forms". I do not know why I see this as confusing. It may be because I am being too literal with my perception of the Table being a complete list of One Specific Entity?

 

What are the downsides to creating them as separate tables? Downsides creating them all in a Forms table?

 

Any further insight could help me make the right choice, sorry if this is obvious.

 

Thanks,

David

[Ender]

For forms that use FileMaker fields to fill in data, I'd recommend basing their layouts on a table occurence of the relavant table that the fields are from. There's no advantage to using a separate set of tables for this.

 

For selecting forms to print, there are some choices. You can simply use different buttons, or you can use a value list (either custom or based on a table). Either way, you may be able to use just one script to run the print routine, branching to different layouts based on the parameter or selected value.

 

The only time I'd consider a table of forms is if we're talking about stored documents (like PDFs or Word documents or something), or merge letters (where templates are stored).

[dmontano]

Hi Ender, thanks for the reply I hope no one thinks I am testing their patience...

 

I am trying to create a rule for myself when it comes to how I build forms. So my apologies if my continued questioning seems like I doubt you or Doug, the fact is, I still have not been able to formulate a rule for myself regarding forms in my head. I am rebuilding my modules from all the advice that I have been able to understand - so the advice I am gettign is very helpful. So, now I am getting to my forms AND letters.

 

From what I gathered on your most recent post, is you agree with Doug that:

1. One table for all forms is just fine. (What if you have a multi-file solution?)

2. If storing docs in the db, then you "would" create a table for each form.

 

Your second comment made me think "what are the possiblities of doing just that - storing docs in the DB?" I am thinking that would actually be fantastic. I do not want to keep everything including the kitchen sink, but since I am creating the data entry fields in FM, then use FM to export to PDF, then email or mail to recipient, I could drop the resulting PDF that is completed by the recipient and drop it into a container - now I have the "original" document along with the searchable tool - FM.

 

I bring this up as I do not want to build one way and find out I should have done it different. Some time ago, I posted a question about creating multiple solutions via multiple files instead of all in one DB file. You responded and included links which really helped me make a decision.

 

I have went the "modular" approach. Knowing I am going "modular" - would your initial recommendation be the same? Can you clarify for me or help me set a rule in my head?

 

Would I build a separate DB file called forms, and have all modules that need forms tie into that 1 DB file? Or, 1 Table in each module (file) that contains all the forms related to that module, or build a table for each form in each module.

 

Thanks again,

David

[Ender]

From what I gathered on your most recent post, is you agree with Doug that:

1. One table for all forms is just fine. (What if you have a multi-file solution?)

2. If storing docs in the db, then you "would" create a table for each form.

Sorry dmontano, either you're misinterpeting me, or your meanings are different from mine.

 

I'm advising putting forms that use FileMaker data in layouts based on whatever table they are about. If it's a form about an Employee, then base it on an Employee TO. If it's a form about an Invoice, then base it on an Invoice TO.

 

While you could put all your forms in a separate file, and use TOs based on their respective tables, this actually makes things more complicated. I would not recommend this unless you know what you're doing and have a good reason to keep the forms separate. In general, it's easiest to keep a form in the file where its table is based.

 

If you're storing docs in your database, then use a single table to hold all docs about whatever. You can use a text field to distinguish between them, or assign a name or description.

[Ender]

As for saving snapshots as PDFs and storing them internally, yes this can be done. This can be useful if it's important to have those snapshots in time, like with Invoices, Purchase Orders, and correspondence that gets sent out to clients. But isn't really necessary if all you ever care about is seeing what the current data looks like, or you have a robust system to view & track changes.

 

I saw a good sample of such a PDF-saving technique somewhere, but I can't recall where.

[dmontano]

Ender, if you're out there - I could use further clarification...

 

Okay it has been 4 months since I posted that thread - let me illustrate how novice I still am. I have these tables involved in my question:

 

vendor_companies

vendor_reviews (join table for vendors / reviews)

reviews

 

When a Review record is created, a User selects more than one Vendor (let's say 3 vendors). These 3 vendors are now associated to the 1 Review via a portal on the Review layout.

 

I am trying to figure out where to build the documents that need to be sent to each of those 3 vendors selected for the Review, such as:

Invitation "Letter"

Request for Information "Form"

 

Re-reading the posts from this thread, I still am struggling because of the manner in which the documents are to be created and distributed. Since each vendor should get 1 letter and 1 form that is customized for them, do I build the forms in the join table?

 

I understand that if I am building an invoice form it should be based on the Invoice TO, however, because my 1 Review record is associated with 3 vendors, I am confused about which TO.

 

I believe I can get all of my Review data from either the Review TO or the Vendor Review TO, however, I want these letters and forms to become a record of the "review" event. I think that means these have to reside in the join table...????

 

Doug had mentioned 1 table for all forms, you suggested to keep it simple and add a layout for the form based on the TO. Does your advice still apply given my scenario? If so, which TO?

 

Thanks

[Ender]

I believe I can get all of my Review data from either the Review TO or the Vendor Review TO, however, I want these letters and forms to become a record of the "review" event. I think that means these have to reside in the join table...????

I'd say they should reside in layouts based on the join table. This way you have easy access to both parent records and the join data.

 

Doug had mentioned 1 table for all forms, you suggested to keep it simple and add a layout for the form based on the TO. Does your advice still apply given my scenario? If so, which TO?

I don't see the advantage to putting forms in a separate table that then has to relate through the current structure.

 

If the forms have a similar format, like "letters", you could consider a user-editable merge letter system, with templates to select from. See these threads for examples of this:

 

http://www.filemakertoday.com/com/showthread.php?t=18574

http://www.filemakertoday.com/com/showpost.php?p=73835&postcount=7

 

 

Putting users in control of the letters makes it so they don't have to give you work requests just to add or modify a letter in the system.

[dmontano]

Thanks Ender - I did come across your posted file and have been looking at it. I like it. I am trying to figure out how this can be applied with what I have been building.