Auto entry for each checkbox selected?

[blewispunk]

I'm new to filemaker, but am trying to automate some data entry into a table based on checkboxes selected for a record in another table.

I'm working on a database of regulations that apply to our business, and in my main table I have some general regulation theme information then checkboxes to select all the regulations that theme comes up in. I then have a second table where I'm listing the specific regulation section references (so one record on table 1 results in multiple records on table 2, depending on the number of boxes checked).

I have it now so that it has a dialog pop up and I enter the regulation and the section reference and it puts that in the second table copying over the categories and themes from table 1, but it seems I should be able to automate this better.

Is there a way to do a "for each box checked loop type function"? Also, I want it to pull over the value selected in the checkbox but haven't figured out how to get it.

[David Jondreau]

This isn't a scripting issue. You need to revamp the structure of your set up. Most of the time, when you need to copy over material from one table to another, your db isn't set up correctly.

Table 1 is "Themes". Table 2 is "Regulations". You need to relate the two. You could do this in a couple ways, the best is to create Table 3 "ThemeRegulationsJoin". Each record in Themes and each record in Regulations needs a unique identifying number (auto-enter serial). The 'join' table has at least two fields, one to hold the Theme ID and one to hold the Regulations ID.

With that set up you can manipulate the data in a bunch of ways.

[blewispunk]

Hey David,

I'm new to this so may not be understanding your response fully, but I believe I have something similar to what you recommend.

I have a table 1 which lists all the regulations and a table 2 that has all the themes. The theme table also has a field of checkboxes for the regulations - which it is getting from the regulations table.

This gives me a nice overview of what regulations pertain to what themes, which creates a 1 to many relationship (1 theme to many regulations). I also want to be able to add the actual page or section number of each regulation in reference to the theme so they can be cross referenced and verified, so I have a third table called "Mapping" that pulls over the theme has a dialog to prompt me for the regulation and the page reference. I enter those and they end up a new record in table 3 (so for each regulation checked in the theme table, I will have a record in the mapping table that just adds one piece of info - the page reference).

That's why I thought I needed to use scripts - I can do this without scripts to create tables with a 1 to 1 relationship. I can see that if I were to enter all the themes in one table and the regulations in a second table I could join these in a third table and add the page number reference manually (having the same theme referenced multiple times for the various regulations) but there are hundreds, possibly thousands of themes so I'm trying to make the data entry as easy as possible.

Let me know what I'm missing or if you have any ideas on a good approach to this.

Thanks!

[David Jondreau]

Can a regulation apply to more than one theme? If that's the case, you have a many-to-many relationship, not a one-to-many.

I'm still a little confused by what you're trying to accomplish, posting a sample file would help. But from your description, this is what I think you need:

You want to be able to look at a Theme and see the relevant Regulations and references. You have one table Themes, each record is a different theme. You have another table Regulations. Each record is a different Regulation (in this table you can store the page number, the actual text, etc).

Then in the third table 'Mapping' each record is a unique combination of a Theme record and a Regulation record (that record just holds the unique id from a Theme record and a unique id from the Regulation record).

The data entry part is creating Mapping records for each Theme/Regulation combo. The easy way to do this is via a portal. But you need to understand the above structure first.

[blewispunk]

I think its slowly sinking in... I've been so focused on how I want to populate the data I didn't pay much attention to the design of the database.

I'm not sure a good way to do example attachments but I did attach 2 screen shots from what I have now (the theme-regulation table and the mapping table).

You're right, I do have a many to many relationship. I was thinking per record where I have a 1 to many but that isn't the right way to think about it.

What I really want to accomplish is to have one table where I can see all the themes and for each theme be able to easily identify all the applicable regulations (that's why I like the checkboxes here as for one theme I can select multiple regs). But this cross-referencing doesn't provide enough info for someone to find the actual part of the regulation the theme is referencing as some of these regs are quite long, so I also want to have the regulation page number referenced so that's why I added the mapping table where all the info on the theme from the first table gets copied over and the custom dialog prompts for the regulation (even though it's the same as one of the checkboxes I haven't figured out how to do this automatically without it copying all the checkboxes - that's why I was hoping to have the script do a "for each" function) and the page number.

From a data entry standpoint, what I'll be continually adding is themes and for each theme I want to be able to easily add the regulation references as well as the page numbers. I don't want to have to jump between tables too much to do this so I'm still not 100% clear on how to move forward but am realizing that I probably should do this in different tables and relate them somehow. Maybe I can have a layout where I'm populating multiple tables from one view?

Have a look at the file attachments and let me known if I'm making any sense.

[David Jondreau]

You're getting on the right track. Your Mapping script needs to create a record in another table for each separate regulation. Each record in that table will have the Theme ID and the Regulation ID. That table, called a Join table will relate to the Themes and Regulations tables. Then you can reference the Regulation table easily.

Figure out how to post your file and I can be more helpful.