Yup, another repeating field calculation question.

[jdu98a]

Ok, here it is in the simplest form I can present it. I have a repeating field with 2 repetitions. The first repetition is a variable value selected from a drop down. The second repetition contains a global value. I need this second value to change based on the value in another field. Basically something like this (this is just to illustrate, I realize the calculation is invalid):

Field 1 [repeating - 2 values]

Field 2

If(Field 2 = "text"; Field 1 [rep 2] = "value 1";

Field 1 [rep 2] = "value 2")

I have tried all kinds of solutions using the Extend function. In fact I can just about get what I need if I make Field 1 a calculation field, but as I need to be able to manually change the first repetition this doesn't work. Field 1 has to be an auto-enter calculation. Also, and this is a big kicker, my repeating field is used in a relationship.

[ESpringer]

First, *why* have you structured your solution around a repeating field, if the first repetition is to be open to data-entry, and the second is to function more like a calculation?

If you could explain more clearly what your real-world task is, I suspect we might find that the best solution does not revolve around a repeating field at all...

[jdu98a]

Ha, every time I post a question the very act of doing so makes me think of my problem in a new way and I end up figuring it out on my own.

In this particular situation a repeating field actually does serve my purpose perfectly. My issue was I was pounding my head against the wall trying to make only one of the repetitions accept my calculation. Then I realized, "wait, it's already set up for only one repetition to calculate. it just has to be the first one." So I simply switched my variable data around from the first rep to the second and everything works great. The first rep calculates correctly based on that second field and all is well.

As a side note, I generally avoid repeating fields like the plague. I'm of the opinion that they should almost never be used. However, this particular solution required a relationship that was able to connect not only a specific group of records but, if needed, all records in a table. The user is able to designate which records are related by selecting a value from a drop down in the related table. Here's what I've done:

Table 1 - Inventory

LineItemName - text field

Category - repeating field [2]

Table 2 - Invoices

CategorySelect - text field [drop down]

LineItemSelect - text field [drop down]

From table 2 the user is able to select a category from the CategorySelect drop down. Records from table 1 that match that selection become available in the LineItemSelect drop down and can be added to the invoice. Because Category is a repeating field in table 1 with one repetition that is always "All Categories", "All Categories" is an available selection from CategorySelect and allows the user to see all line items from table 1. As far as my calculation issue is concerned, I needed to be able to change the value of the repetition that is normally "All Categories" to something else based on other criteria to then give the user the ability to call upon an all together different set of records. This explanation isn't comprehensive but it at least describes what I believe to be a situation that actually does work best with a repeating field.

This being said. I am interested to hear some other suggestions people may have to accomplish this same task.

Edited March 6, 2009 by Guest
typos

[Søren Dyhr]

Also, and this is a big kicker, my repeating field is used in a relationship

But two fields values concatenated with a pilcrow sandwiched in between exhibit same behaviour as relational key, as a repeating field. In my humble opinion would it be easier to get to work.

On the other hand should you study Danieles discoveries here:

http://fmforums.com/forum/showtopic.php?tid/175617/tp/0/all/1/

--sd

[jdu98a]

I was not aware that concatenated fields worked that way in relationships. Good to know.

However, I would ask this. Using your solution would require at least 3 fields. 1 for my variable value, 1 for my constant value, and 1 to concatenate them together. So why do with 3 fields what can be done with 1 repeating field? In my situation what is the disadvantage of using a repeating field?

[Søren Dyhr]

In my situation what is the disadvantage of using a repeating field?

Because you have a problem of controlling which is trigging which and why, mine - it's a kind of breaking things up into components, where each then is nailed down to behave properly ... opposed to the - for me at least weird logic of autoenters in repetitions.

Now what you might miss here is that the concatenation even could be an unstored calc' not taking up any indexing... you might get away just two fields by having the concatenation in the same field like we do phone number formatting with autoenter with the update feature chosen.

--sd