Repeating Calculation

[Gary Brusanowski]

I don't use repeating fields much, so this one's got me beat:

I have one repeating field with, let's say, 3 repetitions. Each rep could have a 1 or a 2 in it. I have another 3-rep field that's a container calculation. If there's a 1 in the first rep in the first field, I want a specific picture to show up (stored elsewhere) in the first rep of the second field; if there's a 2 in rep 1 of the first field, I want a different picture to show up in rep 1 of the second field.

This works fine for repetition 1 of both fields, but how do I construct the formula for the container field so that repetitions 2 and 3 show a picture?

Thx.

[efen]

In another post recently BruceR stated "If you have to ask questions about repeating fields you shouldn't be using them".

[Raybaudi]

Case(

YourRepNumberField [ Get ( CalculationRepetitionNumber ) ] = 1 ; gPicture1 ;

YourRepNumberField [ Get ( CalculationRepetitionNumber ) ] = 2 ; gPicture2

)

[Søren Dyhr]

Daniele why not this way:

Choose ( YourRepNumberField[Get ( CalculationRepetitionNumber )-1] ; gPicture1;gPicture2)

...seemingly a tad faster, or??

--sd

[Brian Panhuyzen]

In another post recently BruceR stated "If you have to ask questions about repeating fields you shouldn't be using them".

Oh, I didn't realize that Mr. Brusanowski asked, "would someone please waste our time while evading answering a legitimate question by quoting some useless response that contributes not at all to the spirit of this forum." Thanks so much efen and BruceR.

Why don't we just revise BruceR's quote to say, "If you have any FileMaker questions you shouldn't be using it." That'd be a very productive philosophy here, don't you think?

[Gary Brusanowski]

Thanks! I'm not familiar with the Get ( CalculationRepetitionNumber ) function and will look into it.

[comment]

Case(

YourRepNumberField [ Get ( CalculationRepetitionNumber ) ] = 1 ; gPicture1 ;

YourRepNumberField [ Get ( CalculationRepetitionNumber ) ] = 2 ; gPicture2

)

1. In a repeating calculation field,

YourRepNumberField [ Get ( CalculationRepetitionNumber ) ]

can be written shortly as:

YourRepNumberField

2. Either way, the formula won't work for repetitions 2 and higher (assuming the globals are NOT repeating). Try:

Case (

YourRepNumberField = 1 ; Extend ( gPicture1 ) ;

YourRepNumberField = 2 ; Extend ( gPicture2 )

)

[Lee Smith]

Hi Brian, and welcome to the Forum,

I'm not going to try and justify the posts mentioned, because the OP didn't explain their reasons for their remarks, however the use of repeating fields, indicate a lot of possible future problems it the person trying to implement them isn't familiar with them. Hence, the caution that they should study them before trying to use them, and know what they are before asking questions about them.

Repeating fields have their uses, I use them all of the time. However, for a New user to open the box and start trying to use them indicates that they need to do their homework, or they are headed down a long and narrow path, one that will require, most likely, a lot of work to undo later.

You only have to do a search for Repeating (repeaters) of this [color:blue]Forum to come up with a summarize statement that would be very much like the one quoted in your post.

Albeit it was short and maybe rude to the reader, the advise is good. In other words, be aware that their pitfalls to using them, and that in the end, there is probably a better way to go then to use them.

Repeating fields still have a place, but most of their need has been eliminated with the arrival of relationships.

Oh, I didn't realize ... snip...

I don't see any help or questions in this remark either. :

Please keep the focus of your post on filemaker, and no personal attacks.

TIA

Lee

[Brian Panhuyzen]

Albeit it was short and maybe rude to the reader, the advise is good. In other words, be aware that their pitfalls to using them, and that in the end, there is probably a better way to go then to use them.

Hi Lee,

I appreciate your response, and do understand that repeating fields are to be employed with caution. However, I do know firsthand of Mr. Brusanowksi's work and let me assure you he is anything but a beginner...the skill level on his profile shows Expert level, and there's no reason to dispute that claim.

There's just nothing useful in efen's reply. It does not help anyone...it doesn't even convey the warning that we should be careful using repeating fields. It's an insult, pure and simple, a personal attack. And I believe that if these kinds of comments are not going to be policed and removed, then they should be condemned, as I did. Someone has to discourage this kind of behaviour. If you don't have anything nice (or useful) to say, etc.

And, just to keep this on topic, I have worked on a legacy database that contained many repeating fields. Originally created to process input only manually, I was asked to add the ability to import data from another source. Data had to be inserted into the second repetition field, something not possible (that I know of) during the initial import process.

I was looking for a simple formula to pull a value from the data in the first field (the second value from a long list of values) and push it into the second repetition...any ideas on this? I ultimately wrote a script to run on the found set of imports that manhandled the data into the second rep, but it was hardly the elegant solution I sought.

I'm now thinking of writing a recursive custom formula to do the trick, something that didn't occur to me at the time, but I'm keen to know of other ideas.

[David Jondreau]

And, just to keep this on topic, I have worked on a legacy database that contained many repeating fields. Originally created to process input only manually, I was asked to add the ability to import data from another source. Data had to be inserted into the second repetition field, something not possible (that I know of) during the initial import process.

I was looking for a simple formula to pull a value from the data in the first field (the second value from a long list of values) and push it into the second repetition...any ideas on this? I ultimately wrote a script to run on the found set of imports that manhandled the data into the second rep, but it was hardly the elegant solution I sought.

I'm now thinking of writing a recursive custom formula to do the trick, something that didn't occur to me at the time, but I'm keen to know of other ideas.

Do you just need to push data into a single repetition or do you need to fill multiple reps? Is the import recurring or a one time thing?

You can use an auto-enter calc that you trigger to set the rep.

Let([

trigger = TriggerField;

rep = Get(CalculationRepetitionNumber);

result = If(rep =2; GetValue(OtherField;2); ThisField

];

result

)

Or a simple Replace Field Contents[] could also work.

A recursive custom function seems like overkill.

DJ

[comment]

You can use an auto-enter calc that you trigger to set the rep.

Let([

trigger = TriggerField;

rep = Get(CalculationRepetitionNumber);

result = If(rep =2; GetValue(OtherField;2); ThisField

];

result

)

I wanted to ask if you have tried this - but then I saw that the parentheses do not match, so I guess I already know the answer... Is there something special about this thread that I am missing?

[Lee Smith]

..snip.. I do know firsthand of Mr. Brusanowksi's work and let me assure you he is anything but a beginner...the skill level on his profile shows Expert level, and there's no reason to dispute that claim.

I can't make a judgement on his abilities or skill lever yet, not enough posts to go by, and he is the one asking the questions, and not providing assistance.

Skill Levels can be misleading. We have had many discussions about their use in the past. I think that most of us use it as an indication of how much detail we need to provide the person we are assisting, and whether they can adopt a solution based on their platform, operating system, and their version and edition of FileMaker. However, the Skill Level can be based on their database knowledge, and not their knowledge of FileMaker.

If indeed he is as good as you say, the Forum will be blessed to have another strong FileMaker Developer involved helping our members out.

BTW, what is your Skill Level?

Lee

[Gary Brusanowski]

Everyone,

Let me back up and actually explain what I'm trying to do for real rather than theoretical. I want to do alternate sorting with my list window column headers, so that when you click a header once, it sorts the list ascending by the field below it; click it again, descending, and I want to show an up or down arrow in each column to indicate this. My thinking was to have 2 rep fields in the table, one for control, and one to display an up or down arrow graphic, where each rep represents a column on the list layout. I want to do it this way because I don't want to end up with 10 control fields and 10 container fields if I have 10 fields on the list screen.

So, in my script, I want to be able to set a rep in the control field to 1 (ascending) or 2 (descending) and have a container in the column header showing the appropriate arrow (in other words, the container sitting in the 3rd column would be a single iteration of the container field, but displaying the 3rd repetition).

My problem is that I don't know how to write the container function. I think the answer is using the Get() function mentioned here, but I plugged it in and it didn't work.

So if anyone can help me solve this particular problem without my having to use more than 2 repeating fields and not have to do anymore scripting than toggle the control field repetitions, I'd appreciate it.

Thanks,

Gary

P.S. And I've actually got "Expert" set in my preferences but it's displaying "Novice" for some reason, at least on my screen. Trust me; I've been doing this for 15 years and have written 2 systems for 2 different Canadian provincial governments, an international music festival, and about 30 other systems, at least. I have a pretty good handle on FileMaker, just not repeating fields! :-)

[Lee Smith]

P.S. And I've actually got "Expert" set in my preferences but it's displaying "Novice" for some reason, at least on my screen. Trust me; I've been doing this for 15 years and have written 2 systems for 2 different Canadian provincial governments, an international music festival, and about 30 other systems, at least. I have a pretty good handle on FileMaker, just not repeating fields! :-)

The Novice is set automatically by the system based on your number of posts. I believe it is an indicator of your Forum knowledge, not your FileMaker Ability.

Below is your profile, and it shows as you selected it.

As an Expert, then I know you are aware of the Table View options for sort. So are you using a List View, or a portal?

Lee

Edited February 18, 2010 by Guest
added portal

[comment]

See if this helps:

rep.fp7.zip

[Gary Brusanowski]

I can't make a judgement on his abilities or skill lever yet, not enough posts to go by, and he is the one asking the questions, and not providing assistance.

Skill Levels can be misleading. We have had many discussions about their use in the past. I think that most of us use it as an indication of how much detail we need to provide the person we are assisting, and whether they can adopt a solution based on their platform, operating system, and their version and edition of FileMaker. However, the Skill Level can be based on their database knowledge, and not their knowledge of FileMaker.

If indeed he is as good as you say, the Forum will be blessed to have another strong FileMaker Developer involved helping our members out.

BTW, what is your Skill Level?

Lee,

Actually, you make a fairly good point about a possible deficiency in forums like this, that your skill level is tied to how often you contribute to the discussion, which I think is wrong. I've been doing this for many years and am hardly a novice, but appear to be so to the other users and a response to me could be slanted a certain way because of that (see the first response if you doubt this; the immediate assumption was that I was new to FileMaker and had no idea how much repeating fields should be avoided, whereas I was programming in FMP v3 and well recall that nightmare!).

I used to participate in forums like this quite often but basically gave up after encountering far too much "beside the point" discussion. This is a technical forum, about a very specific computer program, not YouTube and not some blog. If someone asks a simple question, I think it only appropriate that someone responds with an answer that is factually true (to the best of their knowledge) or just not join the discussion if they have nothing to contribute. I can't tell you how many times I've been asked "why would you want to do that?!?" Well, it's not important why; for whatever reason, this is what I want to do. If someone wants to say, "well, this is how you do it, but here's actually a better way", I would greatly appreciate that; that's how good ideas spread around and we all get better at what we do. But I've been around the block many times in the FileMaker world, and if I jump on this board and ask a question, all I want is the answer. And to be honest, while I would be thrilled to come here more often and help out people with less experience than me, I'd only be willing to spend my time doing that if others would just follow the basic tenant : "I'm answering the question asked and not trying to judge the questioner's reason for asking".

Cheers,

Gary

P.S. I would call Brian an "enthusiastic novice who picks things up very quickly" -)

Edited February 18, 2010 by Guest
fixed format

[Gary Brusanowski]

The Novice is set automatically by the system based on your number of posts. I believe it is an indicator of your Forum knowledge, not your FileMaker Ability.

Below is your profile, and it shows as you selected it.

As an Expert, then I know you are aware of the Table View options for sort. So are you using a List View, or a portal?

Lee

Lee,

Thank you, I didn't notice the profile thing below. And I use the List view as I find the Table view to be very restrictive. In other words, I can do many more things interface-wise with List view than Table view. Then again, I haven't actually checked out Table View in quite some time, so I'll look at it again. Thanks.

Gary

[Gary Brusanowski]

See if this helps:

Doh! :

Thanks!

g

[comment]

your skill level is tied to how often you contribute to the discussion

I'm afraid you're confusing the forum's title with the skill level.

"I'm answering the question asked and not trying to judge the questioner's reason for asking".

Had you spent some time contributing to the forum, you would have noticed that more often than not answering the question as asked is a disservice to the person asking.

[mr_vodka]

What I usually use is a calculation as such.

Case ( Get ( CalculationRepetitionNumber ) = $$num; img [ 1 + $$desc ] )

I use the button to pass a parameter of which field it represents ( and the corresponding rep for the icon ).

If [ Get ( ScriptParameter ) = $$num ]

Set Variable [ $$desc; not $$desc ]

Else

Set Variable [ $$num; Get (ScriptParameter) ]

Set Variable [ $$desc; "" ]

End If

* Edit - I see that others have chimed in already...

Edited February 18, 2010 by Guest

[Gary Brusanowski]

Had you spent some time contributing to the forum, you would have noticed that more often than not answering the question as asked is a disservice to the person asking.

I can appreciate that; advice and guidance are as important as just flatly providing an answer to a question. Let's face it; you just solved my problem in a very straight-forward way and I greatly appreciate it. But if you'd said "why do you want to do that?", it would have been frustrating to me. Not everyone here needs to be taught; some of us are at a level that there are just gaps in our knowledge, things we've never had to deal with, and it would be nice if there was some way to differentiate, rather than painting everyone with the same brush. Hence my earlier confusion over the "Expert" / "Novice" thing. Maybe there needs to be a tag or something that says : "I understand FileMaker very well, but just not this particular thing".

In any case, because of your elegant solution, I'm now re-thinking my staying away from the boards, so thanks for that too -)

g

[Vaughan]

I want to do alternate sorting with my list window column headers, so that when you click a header once, it sorts the list ascending by the field below it; click it again, descending, and I want to show an up or down arrow in each column to indicate this.

In FMP 9 and later this can be done with a script, a couple of global variables add conditional formatting. No fields -- repeating or otherwise -- are needed.

The script sorts the records and sets a global variable to record what the current sort order is. The column headings are text blocks with conditional formatting that change colour depending on the value in the global variable. The arrows to indicate ascending or descending are text blocks too, with the up and down triangle characters (Wingdings or some such symbol font) with conditional formatting to change the text colour to show or hide them based on the value in the global variable.

[Søren Dyhr]

But if you'd said "why do you want to do that?", it would have been frustrating to me.

Have you considered what measure of guesswork that would require, if the replier should hesitate and instead look for tiny cracks and holes in an otherwise sturdy build knowledge surface, without asking at all, as to where the question seems a little illogical founded or seemingly carried by a generalized assumption not as broadly accepted as believed?

It's utterly classic this frustration, it's thoroughly "turned" in:

http://en.wikipedia.org/wiki/Meno

--sd

[Raybaudi]

Either way, the formula won't work

Doh ! :

[LaRetta]

But if you'd said "why do you want to do that?", it would have been frustrating to me. Not everyone here needs to be taught; some of us are at a level that there are just gaps in our knowledge, things we've never had to deal with, and it would be nice if there was some way to differentiate, rather than painting everyone with the same brush.

Hi Gary,

I need a bigger picture to put the request in perspective and decide whether to just answer the question exactly as asked, whether to suggest alternatives, or whether to warn away from such an approach. We try to balance our answers to best fit the question with only a small amount of information. And, ahem, there have been many people who self-rate themselves as Expert or Advanced who couldn't tell you what a Case() calculation does, so your self-rating is only ONE piece of information from which we work.

No matter what you say, some people will be offended, "You didn't give me enough detail," "Why not just answer the specific question" ... someone will be offended thinking we are misjudging them, berating them, laughing at them or that we just don't understand. There is HUGE difference between stupidity and ignorance. Ignorance is nothing to be ashamed of at all ... in fact, NOT asking is the true sin. I cannot tell you how many times we've been profusely thanked for asking further or offering other approaches.

So please, just relax and come around more often. YOu will find that this is a wonderful group of people who truly want to help.

[bruceR]

You most definitely do not know that and you cannot claim to know Efen's state of mind or purpose. You're claiming to be a mind reader in a case where many other less onerous intentions are quite possible.

[Brian Panhuyzen]

You most definitely do not know that and you cannot claim to know Efen's state of mind or purpose. You're claiming to be a mind reader in a case where many other less onerous intentions are quite possible.

I never claimed to read efen's mind; I claimed that his response was an insult. That was my interpretation. Also, the OP was insulted, ergo, it was an insult. If efen's intentions were otherwise, the content of his message failed to properly communicate those intentions.

By the way, "onerous" means "involving an amount of effort and difficulty that is oppressively burdensome"; I can't see any way the adjective could be correctly applied to intention. Did you mean perhaps "dismissive" or "antipathetic"?

Thanks to everyone who did provide constructive responses to Gary's query and my own. I know that Gary is pleased with the suggestions he received...I haven't had a chance to try the ideas for my issue...I hope to have time this week!

-B.

[efen]

I never claimed to read efen's mind; I claimed that his response was an insult. That was my interpretation. Also, the OP was insulted, ergo, it was an insult.

... and I feel insulted that you consider my original reply an insult: "ergo, it was an insult".

...efen's reply. It does not help anyone...it doesn't even convey the warning that we should be careful using repeating fields.

The reply may be succinct but it warns - obliquely I admit - in the words of another poster(an experienced developer) against using repeating fields. It most certainly was not intended to be an insult or personal attack, but if you consider it as such then I probably won't be able to dissuade you, whatever I state.

I do not spend time on the forum to insult people. I try to help others to solve their FileMaker problems and answer questions; I also learn much from others' answers - there are many knowledgeable individuals who post regularly here.

I look forward soon to be reading your answers to questions raised here.

[Brian Panhuyzen]

The reply may be succinct but it warns - obliquely I admit - in the words of another poster(an experienced developer) against using repeating fields. It most certainly was not intended to be an insult or personal attack, but if you consider it as such then I probably won't be able to dissuade you, whatever I state.

I'll accept that you did not intend your message as an insult, but then it's matter of failing to properly communicate your intentions. Succinctness is all very fine, but not at the expense of clarity.

I recommend better care when replying (especially to us sensitive novices). Messages that don't provide information, request more data, or offer a solution (or gratitude for a solution) should be avoided, as they don't contribute to the topic.

(And by that criteria, this message is disqualified.)

We've flogged this enough; I'm ready to move on to helping or being helped with FileMaker issues.

~B.

[Ocean West]

I would like to step in here into the discussion and please request that if you do have constructive suggestions & feedback as to how we can better facilitate a method to differentiate people's skill levels and provide such detail to other who would respond, in order to shape the level of detail required to answer the initial topic. Please feel free to email me or leave a suggestion in the Feedback forum.

Regardless of "skill/experience" of using FileMaker or this site. It is of mutual benefit for all to be as succinct or as verbose to clearly paint the entire scope the question and or the answer / suggestion that you are making.

Please keep replies ON TOPIC, complementary, contributory, and constructive and clear, we can see that there are differing opinions how something can be achieved, the purpose of the forum is to express your findings as you may have explored each method, provide pros & cons to each method explain why one method may be more accepted vs the other.

Educating the user as to the functions and features and examples speak volumes as to best use FileMaker, it is ultimately decision of the person doing the implementing as which to choose.

Let's remember to all play nice!

Thank you

Stephen

[Tips & Suggestions]

If typing is not your cup of tea, record a short video / audio clip http://www.jingproject.com/ or call yourself on GoogleVoice to have it dictate your message, - or perhaps a dictation software?