Copying tables - a good thing or not

[Nicnacs]

Hello

I'm new to version 7 although I've been using Filemaker for over 10 years. There seem to be some fundamental differences and I wondered if people had advice on the best way to do something. It seems I'm not the only one out there with this idea but several people have said that copying tables isn't the best way to go about things.

I'm using a solution from Queue that allows you to count how many times an option is selected in a repeating field. (http://www.fmforums.com/threads/showflat.php?Cat=0&Number=134738&an=0&page=0#134738)

However, I'm having problems getting it to just evaluate the results for a found set of records. So I was thinking what about exporting the found set into a pretty much identical database. Seems a bit cack handed but it's the only solution I can think of.

Then I thought, but version 7 allows you to create tables within the same database so let's copy the table, export the data to that, get the results and then return to the original table.

Unfortunately there doesn't seem to be an easy way to copy a table. And there are several posts that suggest this isn't the best way anyway. So what would be the best way? Use the Relationships tab in Define Database to create a new table entry? Is that the best way?

Thanks

Nicnacs

[Søren Dyhr]

Copying a table, is kind of defeating the object of relational design, so it's a designflaw in your approach. That being said must you also be aware of the dangers of exploiting repeating fields as means to store data, it is wrong in the sence it breaks 1NF (first normal form) that dictates one fact per field.

I wouldn't deny that repeaters have been given a major overhaul designwise with the latest version, but it's gross ignorance to take them up again as datafields. The added features have most likely arrived to make OR-keying more flexible, than say a pilcrow delimited keyfield.

What we miss here is why you suddently after so many years developement starts to find repeaters attactive? Since Queue and Ugo in the purest propellar head exitement, neglect the arsonistic consequences of their behaviour - on laymans approaches to data handling.

You're supposed to take advice given in free forums like this, with a grain of salt will they say to their defence ...since they're given free is it synonymiously given "AS IS" eventhough you might loose wagonloads of money on a single dodgy advice.

I can only apeal to posters not to use snakeoil salesmans methods, since "The Land of Plenty" certainly encourages such behaviours garnished with claimed right to a "Jamesian" innocense.

--sd

[Ender]

Except for the snakeoil, I have to agree with S

[Nicnacs]

Wow! Not sure I completely understand all you're saying....

I'm using repeating fields because the solution suggested does exactly what I want to do and seemed like the sensible choice. I've not used repeating fields before so I've not returned to them. I was a bit hesitant at using them but as I couldn't think of an alternative solution, I thought I'd give them a go.

Another occurrance of whether to copy or not has just occured. I have a database that I'm using to collect data about events and training events. At the moment, Events and Training are two different tables and each has an associated Evaluation table. These Evaluation tables are essentially identical - we ask the same info on gender, ethnicity, age, whether they liked the event or not for both Events and Training.

So why can't I copy the Event Evaluation table to make the Training Evaluation table - with the necessary modifications of course?

Nicnacs

[RalphL]

The alternate solution to repeating fields is a portal and a related file. There are places to use repeating fields but generally not for data. They may fit your current needs, but fall short on a future need. While you may not have returned to repeating fields you have taken a step in a backward direction.

You can make a copy of a table. Use FMRobot to copy the fields. Use copy and paste to do layouts. Use script copy to copy scripts. You can also duplicate files.

[Ender]

If your evaluations ask the same questions, I would recommend using the same table to track both (unless there is a compelling reason not to.) You can distinguish between the two by using a Type field.

[Ugo DI LUCA]

Agreed there is absolutely no reason to use repeaters for this purpose.

Actually, in Queues example, the Repeating field was an alternative to a Count of non repeating values within a file, which result was displayed as a repeating field, each value in a single repetition. So it was actually not used for any storage.

This said, I strongly recommand you use portals for this, even when working with foundset as FM7 now handle this too.

[Nicnacs]

OK I'm clearly not understanding how all of this is working! I found Queue's solution and thought it did what I wanted to do so used it. SLightly apprehensive as I didn't actually understand how it worked but couldn't work out how else to do it.

Here's what I'm trying to do, maybe someone could point me in the right direction.

I work for a small charity and we need to collect evaluation data from people we work with so we can report to our funders. For example we run training events and need to collect equal opportunities data from those who attend: sex, age, ethnicity, whether they're disabled or not, sexuality, and so on.

I started creating a database thinking I could create fields for all of these things with associated value lists to allow people to choose what sex people were when entering data. What I couldn't work out was how to count up how many men and how many women, for example, from the data.

Has anyone got any suggestions?

I guess I should start this as a new thread as the topic has changed. I'll go do that!

[Ender]

The simple way to get counts of the various entries in a field is to place the field in a sub-summary part, along with a Count of RecordID summary field. When the found set is sorted by the field and the layout is viewed in Preview Mode or printed, the sub-summary part will show the counts for each unique entry.

There are plenty of previous threads about sub-summary reports in the forums, and the FileMaker Pro manual shows a step by step example.

Just curious: what kind of data would you track in a 'sexuality' field??

[Nicnacs]

See that's the problem if you don't know what the technical terms are to search with! I'm not sure I understand your solution but I'll take a look at some of the threads about sub-summary reports. Unfortunately I don't have a manual.

Sexuality: you know like straight, gay, bisexual.....

[DykstrL]

Actually, if you have the install CD, you DO have the manual. There should be a folder called Electronic Documentation. In there you will find a Users Guide, which is a PDF of the printed manual.

[Ender]

PDF manuals are also available on filemaker.com.

BTW: I would probably call that field "Sexual Orientation".

[Nicnacs]

OK so I've found a manual (well tutorial) and worked through the example and created a test database to see if I could get it to work. But instead of counting how many times a particular entry is selected from a drop down list for each field, it just counts the fact that there's data in the field. I think it's because I don't quite understand what you mean by "place the field in a sub-summary part, along with a Count of RecordID summary field".

I defined a field "Enquiry ID" and a summary field "Count of enquiry ID". Then when I was creating the report, I used date for Report Categories (as I want to be able to count things by date).

I've attached a zip of the test file in case that helps. It would be great to get some help on this. I don't think anything in Filemaker has caused me so many problems. I thought I understood how it worked but now I'm losing my confidence!

Thanks

test.zip

[Ender]

Put the Count field in the part where you want to show the breaks. If you're counting the number of records for each Date, then the Count field goes in a Sub-Summary by Date part. When sorted by Date and previewed or printed, the sub-summary shows the count.

I've adjusted your test file to show how to use the same layout for sub-summarizing by Date, Topic, or Date and Topic. Hopefully you can generalize the technique to build your own report.

test-5v2.fp7.zip