Repeating Field Relationship Not Working

[bac mac]

The personnel table includes a repeating field where up to 6 required training courses (Courses Taken) that have been completed may be entered by dropdown from the course name field from the Training Courses table.

In the Training Courses table, there is one record for each course and each course has a unique name (Course Name) and also a field for frequency required (e.g. once, annually).

In the Personnel table, I can't seem to get the repeating field Frequency to pick up the frequencies from the Training Courses table, although I do have a relationship between the two tables linking Courses Taken and Course Name.

The desired result in the Personnel table should appear as:

Citi Training-----Annually

Cyber Security----Once

Safety----------Annually

I tried accomplishing this via calculations and scripts to no avail- I can loop in the script, but I am missing how to enter Annually into Frequency[1] based on a relationship or lookup from Courses Taken[1], if so, I could repeat this down the repetitions in a script.

What is the best way to fill the Frequencies in the Personnel Table as shown above?

Thank you

Edited January 15, 2008 by Guest

[Søren Dyhr]

Ditch repeaters and normalize your structure instead. Who or what have persuaded you to check out repeating fields, it's pithole number ONE, you've be caught in. Repeaters are not for storing data in, but instead for utility use, such as relational keys.

So the direct question to you, why isn't your data in a portal, please argue for deliberate disregard of portals??

--sd

[LaRetta]

Deliberate disregard of portals? That is quite presumptuous. The reason is usually ignorance which is something NOBODY should EVER be ashamed of and I suspect that the 'idea of repeaters' came from FM itself in their sample files. You have sure been coming on strong lately. I suggest you focus on offering more appropriate alternatives (for storing data), recognizing that not everyone has your vast knowledge. bac mac has nothing to defend here. Soren, you are much nicer than you came across in this post. :wink2:

Edited January 15, 2008 by Guest

[bac mac]

I'm open to any alternatives. I use portals elsewhere in the database, but since I'll additionally need to execute some calculations involving two additional parallel fields, I thought that I needed to use repeating fields.

The plan for this part of the database explaining more:

Courses Taken--Frequency--Completed--Due

Citi Training----Annually---12/1/07----12/1/08

Cyber Security---Once------11/1/07---

Safety----------Annually---1/1/08----1/1/09

Workplace------Semi-annually-7/1/07--1/1/08

If I can get the Frequency items into this Personnel Table from the Training Courses Table, I have no problem generating the calculations to generate the dates Due. The Completed dates are user entered, the Due dates will be calculated based on Completed and Frequency.

There will be different numbers of courses taken in different personnel records. As I mentioned, every course has a unique Course Name which appears in the Training Courses Table and is selected by the user in from a drop down referencing a value list of the Course Names in the Training Courses Table. Courses may change over time and may change their frequency requirements. If they change, the frequencies should update everywhere. New courses may be added in the future.

What's the recommended method to get the Frequency info associated with the Courses taken so that I can operate on it?

Thank you,

Bruce

[Lee Smith]

Come on Soren, lighten up. This question would be more appropriately directed at FileMaker, not a new user of FileMaker, who has innocently thought that they could be easily converted to their own needs.

So the direct question to you, why isn't your data in a portal, please argue for deliberate disregard of portals??

--sd

I agree with LaRetta, you are too nice of a guy to express your thought about Repeating Fields like this.

Lee

[Søren Dyhr]

Alright I could be nicer - Sorry!

A table is missing the structure is many to many IMHO, The calc should be an unstored calc'field in the join-table since the dates from and to belongs to each course right?

The calc is pure and simple formatting data a relation or more away. This could talk for the omission of the jointable, but it's better to be repaired if the enrollment suddenly needs some kind of statistics.

The unstored calc'field in the join table could contain this:

Let(tt=Date ( Month ( course::FromDate ) ; Day ( course::FromDate ) ; 1+Year ( course::FromDate ) ) ≤ course::ToDate;Choose(1+tt-IsEmpty ( course::ToDate );"Once";"Simi-Annual";"Annual"))

Which then would be placed next to the course name pulled in via the courseID which would be the first field in the portalrow.

It's important to realize that reformatting of data, is by and large redundant storing if you were to save it as well, with a lookup, this was what made me loose my temper - let be that people overuse repeating field, but if lookups are mentioned as well ... I apologize I should have explained my self instead!

--sd

[Breezer]

Talking of repeating fields, It is out there that Filemaker still has a Time Card Template in FM9 that extensively uses and seems to advocate the use of repeating fields, why is that?

[LaRetta]

Actually, they have changed their templates within the last 6 months or so. Now they are relational; although somewhat weak. Regardless, I'm pleased they FINALLY ditched the repeaters for data.

UPDATE: Actually, I think I just checked their invoices/lineitems/products template. I don't recall checking their timecards one. In truth, I avoid their work because it might rub off on me. :)

Edited January 15, 2008 by Guest

[AudioFreak]

LaRetta,

I just checked the Time Card template and it is still using repeating fields.

Michael

[comment]

The Time Cards template uses repeating fields for the times, and Day1, Day2, Day3... for the days. Looks like they tried to show every possible way to mess it up.

[Søren Dyhr]

The objectives are pretty obvious ROI for the tool buyer, if you sell a copy more of filemaker with these things workgroups more or less can piggy-bag upon, are the mission accomplished for the marketing people in filemaker.

They're intuitive to use, and you simply realize the dangers they involve way too late. Because what happens, is that people outgrow their need for these business templates and start dipping their toes into real development.

The success seems immediately there and the notion of getting a handful of trick here and there seems all there is to accomplish what's desired. How come then, that Hernandez in "...mere mortals" teaches never ever to accept and build on the legacy base's relational structure.

Because relational theory aim is to avoid making tradeoff on previous tradeoffs ... the issues gets burried under layers of complexity the developer unfortunately are likely to loose track of.

There is a Joyce quote on this:

“Irresponsibility is part of the pleasure of all art; it is the part the schools cannot recognize.”

In other words, if you're going to break the rules or improvise ... make sure you know all the rules ahead of the attempt. The structure does indeed benefit of recognizable patterns instead of a hefty brew of interdependent calc'fields, because it's so much easier to maintain half a year later or more.

Deliberate denormalizations should not be the rule, but instead be put into crucial details, by this formula:

LEST FOOLS SHOULD FAIL

True wisdom knows

it must comprise

some nonsense

as a compromise,

lest fools shouls fail

to find it wise.

Which is a Grook by Piet Hein:

http://www.chat.carleton.ca/~tcstewar/grooks/grooks.html

--sd

[comment]

I believe you are flattering them by assuming they did it on purpose.

[Søren Dyhr]

I'm pretty sure, Ryan Rosenberg in my following URL have made a point or two when LeCates and gang suggested that they could come up with an migration tool that made portals out of a repeater, by dropping the previous solution ontop of the filemaker-icon.

http://www.filemaker.com/ltc_interview/09-22-2007

Populists are not necessarily fools, only cowards!

--sd

[LaRetta]

:content: I like this Piet Hein. His writing is concise and beautiful. Thank you for the link!

[Breezer]

I just started using it, should I ditch it while I'm still ahead? I did a few modifications but left the repeating fields as-is (my assumption was if FileMaker is using repeating fields, then it must be okay), however, my users have not yet started using. I can burn some midnight oil to make it relational before the scheduled due date.

Please advice if I need to go ahead and make it relational so as to avoid future headaches.

[Fenton]

It is difficult to say whether repeating fields for data is wrong or OK. It depends. One thing you can say about using them for real data is that they have limited use. Whether those limits are a problem or not depends on the specific situation. In the case of the Timecard, I would say that the use of repeating fields is not "wrong." They have solved the problem of storing the data, as it is, and kept the total as a stored field. This is the "pro" of the situation, and could be significant if you accept the limits.

But I still say it is "not so good" as a template. Because what they have not made clear is that it is pretty much a dead end. If you decide you want to add functionality, like "what task did you do during this time," and then want to summarize or report on that, you will not be able to; and if you're a beginner, you will not know why, probably thinking you're doing something wrong. But it will be because the structure is inadequate. At the very least they should explain all this; but that's like asking a company for more informative warning labels on their products.

I personally do not like to paint myself into a corner. In fact that is how I first learned about relationships, by using repeating fields and getting stuck. BTW, FileMaker has made it pretty easy to convert a group of repeating fields into a real table. The Import dialog will ask you if you want to split them into separate records.

Add a parent ID to that. create a relationship from the parent table, and presto, relational table. Simple redefine the repeating fields on the layout as the new related fields, draw a portal around the fields (which are now 1 row), specify # of portal rows, and you're ready to go. Looks the same, acts much the same.

Edited January 16, 2008 by Guest
not so good

[Søren Dyhr]

In the case of the Timecard, I would say that the use of repeating fields is not "wrong." They have solved the problem of storing the data, as it is, and kept the total as a stored field. This is the "pro" of the situation, and could be significant if

I agree here, this is indeed why I think it's done on purpose - since it takes ages for the new user acquainted with the workings of the RG ...at least to know enough to do the same with a relational approach and cut-up portals. I'm not with the ones who're saying the entire RG- approach sucks ... but it just takes some getting used to - which could be bad for business seen from the FMI perspective, if it was important to master before heading into the tool.

Even-though I kind of leads the "ditch repeaters" movement here, would no-one consider filemaker as an option if they didn't attempt to go ahead with the misnomer "database"

FMI can't be laying on stomach of pure obedience to technological demands only ...is a pretty sure recipe for bankruptcy - all technical problems are people problems, lack of throughly established skills in abstract thinking and method as the most dominant.

But when the problem ends up here, with people in despair over all the work they've put in getting in to a cul de sac, would I say that it's important for me to denounce what I believe is wrong. Getting the baddies early is good strategy - if I should para-phase what Norman Winn wrote to me in a private message.

--sd

[comment]

I still think whoever made that template simply didn't know any better (why use individual fields for some columns and repeating fields for others?). But basically I agree with Fenton: as long as you are aware of the limitations and willing to accept them, repeating fields can be a valid choice.

[Breezer]

I absolutely appreciate all these great ideas, I feel more intelligent now that I have absorbed views from all of you. I will work on the Time Card and if I like the final product, I may post it here. Fenton, Comment, Soren and all are awesome.

[bac mac]

The complication for my database is that there are multiple Course Names in every personnel record, each of which needs to be associated with the single Frequency for that Course Name from the Training Courses Table. If it wasn't for the multiple- single relationship, it would not have been a problem because a table relationship between the Course Name in both tables would have led to the appropriate frequency. I think I'm just trying to obtain in table A, given value X (one of the course names) in a record in table A (the Personnel table), Filemaker, please provide the value of the Frequency field from the only record in table B (the Training Courses table) that has the course name = to value X. And to get that answer without running a script. It seemed as if it should have been more obvious in Filemaker but I'm still learning.

With respect to the problem of bringing in the Frequency, from the Training Courses Table, that the course must be completed for each of the courses that a particular Personnel record shows, I appreciate everyone's suggestions and I'll see if I can do it without repeating fields.

Søren- Thanks for the example calculation. I tried to implement that Let and Choose calculation but a couple of quick tries didn't get it working yet, but I should have more time tonight to play with it.

Thanks,

Bruce

[Søren Dyhr]

Take a look at the attached template...

--sd

Enrollment.zip

[bac mac]

Søren- I appreciate the example that is a much more elegant approach with the intersecting table!

To better understand the details, I generated a similar database from scratch following all of your field setups, etc. The hardest detail to find, but I finally found it, was checking the allow creation of records via the relationship.

Thanks very much,

Bruce

[Søren Dyhr]

Thats a bit worrying, since you said:

I use portals elsewhere in the database

Join-tables, are the standardized way to build many to many relations as well as the safest if you suddenly should wish to make some statistics. Invoicing is probably the place where it's used the most - check out the template I here link to:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000717

But what are rules without exceptions?

http://fmforums.com/forum/showpost.php?post/265338/

...but study carefully Enders argument against it as alternative to the classic way:

http://fmforums.com/forum/showpost.php?post/265346/

--sd

[bac mac]

Søren-

I do, but not as well as I could. For example, in this same database, one portal in the Personnel layout displays all of the projects that an individual is listed on through a tunnel to the Projects table. But I haven't been taking advantage of all of the options available in relationships.

I'll take a look at the examples you provided links to.

Thanks again,

Bruce

[FM_SouthAfrica]

Hi, saw your post and was wondering if you had seen one on 'One to Many' relationships with the exception that the MANY is stored in a repeating field . . . ? (yes, yes I know, but when I explain the reasons why you will understand . . . )

Edited March 4, 2008 by Guest