self-relationship of a repeating field

[Steven Cappiello]

Hi,

Is there a way to relate a repeating field to itself, so that the relationship will be true if one of the repetition values exists in any repetition of that field in any other record?

For example

record 1 repetition 1 - value "apple"

record 1 repetition 2 - value "grape"

record 1 repetition 3 - value "pear"

record 1 repetition 4 - value "banana"

record 2 repetition 1 - value "peach"

record 2 repetition 2 - value "orange"

record 2 repetition 3 - value "apple"

record 2 repetition 4 - value "peach"

so, in this case, on record 1, the 1st repetition would indicate that it was related to record 2, repetition #3, and vice versa.

This is only an example. The reason I want to do this is that a coworker has an old database that uses a repeating field to enter invoice line items on a check request form. She'd like a little warning to pop up if she's ever written a check request that matches the vendor name and the invoice #.

Of course, I wouldn't have used a repeating field here, but if the above is possible, I'd like to go that route because the database is going to be discarded in a month or two and because of that don't want to go through the trouble of rebuilding it and figuring out a way to get all the repeating data into a portal. (although I suspect that might not be so difficult)

any advice would be appreciated.

Thanks.

[Søren Dyhr]

No - it's when ever a match might occure regardless of the rep. number. There are no indication of which!

The relation works similar to a pilcrow delimited value in a text field, the reason for the occational building of relations via repeating fields is usually down the ability to make the reps. progress from rep. to rep. ...we jokingly call this the poor mans recursion, because you can produce a range of dates in a repeating calc'field like:

Case(Get(CalculationRepetition) + Extend(startDate)

--sd

[comment]

A repeating field, when used as a match field for a relationship, acts just like a return separated list would act - any single value that matches any single value in the other TO will make that record related.

Indicating which repetition is "active" is a bit more tricky. First, if there's another repeating field on the other side of the relationship, you would need a custom function (or the new-in-8.5 List() function) to concatenate the related values. But the bigger problem is this: what if there are SEVERAL related records? I don't think there's a way for each repetition to tell you something like "I am related to records 1 and 3, I don't know about the other repetitions".

[comment]

There are no indication of which!

There could be - if, for example, you check the repetition value against a list of related values.

[AudioFreak]

Or just make a new table and import all the line item info to it and set up the relationship......lol

But you guys seem to be having fun :

[Søren Dyhr]

Yes Audiofreak, he's warming up:

although I suspect that might not be so difficult

...what you didn't get was that the solution is going to be ditched very soon!

--sd

[AudioFreak]

Actually I did see that. But it's probably faster to ditch the repeating fields now rather then to try to find a way to work with them. Then work on the rest of the solution when time permits.

Michael

[Søren Dyhr]

it's probably faster to ditch the repeating fields now rather then to try to find a way to work with them

Mechanically yes, but reluctance is usually a set of mental blockings - the art of getting things done. Blockings which somebody can work an entire lifetime to get rid of, successfully or not.

Newbe developers who have made an immense efford in developing a solution with endless numbers of fields and numerous repeaters utilization, are utterly proud of their achievements, and would hate to change their ways.

I have ever so often, poked into such first attempts, learning what always is asked for really, just are tiny bits ingenuity to overcome an obstracle, and an stubborn disbelief that this area actually is tied to a science, dictating a different approach.

I must admit that I often lack the courtesy, to break this conclusion ...gently enough. The thing to me is foreign the language, Bruce Robertsson can like a police officer, say don't - while I often makes it a hurtfull expirience to the reciever.

--sd

[DikkeHenk]

I am rather new on this forum and I do not want to start such a thread. But please show some respect for newbe developers. It seems you are expressing some frustration in your answer.

Henk

[Genx]

Lol, Its ah Soren, and unfortunately most of his posts are filled with frustration (especially when the topic has anything to do with repeating fields). The best we can suggest is don't take it to heart (I think this should be on the front page hehe).

He does have good intentions, but is sometimes a bit rough in his deliverance of them -- Though make sure you do actually read his posts.

[AudioFreak]

Newbe developers who have made an immense efford in developing a solution with endless numbers of fields and numerous repeaters utilization, are utterly proud of their achievements, and would hate to change their ways.

I honestly don't blame people for making this mistake, hell I made the same mistake with my first file(Purchasing Database).

Filemaker leads newbies into making this mistake from the first day they purchase Filemaker. The sample purchasing file is basically a flat file no relations with a bunch of repeating fields for the line items.

How can they know this is not a very functional way of doing this? They can't. They don't learn this til later when they want to sort the line items(Like I did)and they can't. Or wanting to see a list of all the times a specific item was ordered. They learn the problem with this structure later on(Usually here or the Cafe).

Filemaker should at least have a related version of a purchasing file to show the proper structure rather then steer new developers into the wrong way of doing things. It is Relational software afterall.

Why not lead them this direction with sample files?

Just my rant.....Yes I hate repeating fields too......lol

[Søren Dyhr]

Filemaker should at least have a related version of a purchasing file to show the proper structure rather then steer new developers into the wrong way of doing things. It is Relational software afterall.

Yes such an expressed ROI concern, is probably molded in the marketing dept, where only the number of sold copies really matter. Sometimes are we at the other end of the rope eagerly hoping to cash in on our developement skills, acting with similar blinkers attached.

We are similar urged to focus on structure, structure and structure even more than we would please! ...and not get lost in nifty and cute crincles that tear filemaker out of it's realm ...which just as well might raise wrong hopes for ROI among the green developers. You can really with ingenuity make Filemaker jump hoops!

So the frustration of mine is NOT aimed towards the new developers, but instead an urge to fellow repliers to hessitate, when tiny bits of inginuity is required. Play this fair, and tell with your fluency in english, that a propper structure would be better!!

--sd

Edited June 11, 2007 by Guest

[Steven Cappiello]

wow.. I just checked back and was surprised to see so much activity on this thread....

first of all.. is anyone else NOT receiving email notifications when there is an update to their post??

secondly.. I'm glad repeating fields related to themselves will return a match regardless of the repetition # in the child repeating field.. for some reason I didn't think that was the case...

thanks for setting me straight