Modifiable Repeating Field Entries?

[steveald]

The main database I work with contains thousands of records - one for each customer we have ever worked with, and a new record is created for each new customer. And there are dozens of layouts similar to the one I am working on now - all available for, and unique to, each customer record. The portion of this layout I am working on is much like an Excel spreadsheet, with a header row of values across the top, a column of ages down the left side, and a grid of values each calculated based on the value above it and the age to the left of it. See the attached example. I make use of repeating fields for every row on the grid to avoid having to create hundreds of individual calculation fields.

To do this, I have a repeating global field (let's call it g_Benefits) that contains a list of numbers: 10, 20, 30, ..., 100 for the example shown. The header row is another global repeating field that calculates values by multiplying the values in g_Benefits by 1,000 and displaying the results as dollar amounts. Each row below that is another repeating, but non-global, field that calculates and displays values based on the values in g_Benefits, the number displayed to the left of that row, and other variables specific to the customer in that record. So, while the header row and the Age column seen in the attached example remains the same for every record in the database, the non-bold values in the grid will differ from record to record.

But, now I need to allow for the possibility of entering a number in a custom value field that will replace the corresponding value in g_Benefits. (10 repetitions in g_Benefits means there will be 10 custom value fields.) And the custom value must be constrained to the record it is entered in (so g_Benefits can no longer be global). If I entered 25 in the 2nd custom field in one record, g_Benefits would change to 10, 25, 30, ..., 100; while the other records (and any newly created records) would keep the original values. And 2nd column of values in this layout's grid for just this record would change based on the custom value entered.
 
This creates two issues.

1. I need to configure g_Benefits so it populates with the default list of values whenever a new record is created and keeps those values in all existing records.

2. And I need to configure g_Benefits so that, if a value is entered into a custom field in one record, the corresponding cell in g_Benefits changes for only that record. 

I've been working with the Get ( CalculationRepetitionNumber ) function, but I haven't been able to get it to populate any more than the first repeating field. Even then, I'm not sure that would allow me to replace values with custom values.

This is all done in Form view. There are no portals involved, no scripts, no virtual lists, and no related tables.
 
Any thoughts? Screenshot attached.

Edited January 21, 2016 by steveald

[comment]

I am afraid I got lost in your explanation. I think you are asking how to auto-enter default values into all repetitions of a repeating field. If so, have a look at the attached file.

AutoPopulateRep.fp7

 

 

Edited January 21, 2016 by comment

[steveald]

No, sorry.

I have functioning repeating fields, one each for every row on the sample screenshot I attached. What I need to be able to do is change individual values in the initial repeating field that all these repeating fields are calculated from. And the initial repeating field can't be global anymore because the change needs to be limited to the record it was made in.

Thanks for the example, but this needs to work in Form view.

[comment]

 

17 minutes ago, steveald said:

Thanks for the example, but this needs to work in Form view.

That makes no difference. All of this works at the data layer. The way you view it is irrelevant.

 

17 minutes ago, steveald said:

What I need to be able to do is change individual values in the initial repeating field that all these repeating fields are calculated from. And the initial repeating field can't be global anymore because the change needs to be limited to the record it was made in.

I have the feeling you do not understand the principle being demonstrated here. Each record, upon its creation, inherits the values stored in the gDefaultValuesR field and places them in the LocalValuesR field. LocalValuesR field is that "initial repeating field" from which you want "all these repeating fields" to calculate. It is not a global field, and you can change any or all of its values, and the change will affect only that one record.

I have not included any of the calculation fields, since they are not necessary to demonstrate the technique.

An alternative method could be to have a global field with the default values, a local field that stores only overriding values, and change all your calculation formulae to consider either the default global value, or - if it exists - the overriding local value.

Or perhaps I don't understand at all what you want.

 

 

Edited January 21, 2016 by comment

[steveald]

Thanks. I'll see if I can work your suggestions into my database.

[bruceR]

Meanwhile note this is another XPOST. Same discussion on Filemaker Community.

[steveald]

Is that a problem? I'm getting two very different responses from different posters.

[LaRetta]

It is not a problem at all, Steve.  It is good to put [XPOST] in your subject so if people have read your post at another forum, they can skip it here if they wish.  But you are correct that sometimes different respondees frequent one forum or the other and you can get additional answers.  :-)

And not everyone knows this kind of subtle 'forum etiquette' so you did nothing wrong. 

[steveald]

Thanks, LaRetta. I'll try to remember to add that next time.

[steveald]

22 hours ago, comment said:

 

That makes no difference. All of this works at the data layer. The way you view it is irrelevant.

 

I have the feeling you do not understand the principle being demonstrated here. Each record, upon its creation, inherits the values stored in the gDefaultValuesR field and places them in the LocalValuesR field. LocalValuesR field is that "initial repeating field" from which you want "all these repeating fields" to calculate. It is not a global field, and you can change any or all of its values, and the change will affect only that one record.

I have not included any of the calculation fields, since they are not necessary to demonstrate the technique.

An alternative method could be to have a global field with the default values, a local field that stores only overriding values, and change all your calculation formulae to consider either the default global value, or - if it exists - the overriding local value.

Or perhaps I don't understand at all what you want.

 

 

Well, I thought I had it figured out. I replicated your gDefaultValuesR and LocalValuesR in my database, and tried to configure them the way you did. But, when I created a new record, nothing happened. I'm sure I missed some small detail. Take a look at the attached screenshot collage and see if you can tell what that detail might be.

As you can see in the top images, I already had the equivalent of your Table, Table 2 set up with a relationship for Serial Number established between them. And I set up the Looked-up value for my local repeating field just like yours. But, as you can see in the lower left corner, when I created a new record - nothing happened in the local field. Any ideas what I'm missing?

If it makes any difference, this is all happening on a database hosted through FileMaker Server 13.

[comment]

My relationship uses the x relational operator.

[steveald]

I found a little bit of information on the "x" relationship - including a post of yours from 2008.

Can I safely change the "=" to "x"? Or would that mess up the following:

My self-joining relationship was set up to make a handful of calculation fields work, such as the header row field (we'll call MPs) in another grid-type layout which uses the calculation:

Let ( [
n = Extend ( MP Min ) + Extend ( MP Inc ) * ( Get ( CalculationRepetitionNumber ) - 1 ) 
] ;
Case ( n ≤ Extend ( MP Max ) ; n )
)

Making that work required setting up a Value List item where the Source was From Field and the Values were Field: "SameRecord::MPs" (related values only)

This was necessary because the values displayed in that field are based on record-specific settings that control the values in MP Min, MP Inc and MP Max.

Edited January 25, 2016 by steveald

[comment]

15 minutes ago, steveald said:

Can I safely change the "=" to "x"? Or would that mess up the following:

I would have to understand "the following" before I can answer that - which I don't.

If you have a relationship to the same record, and you need it to be to the same record, then of course you cannot change the relational operator to x. That would make all records in the table related.

But I don't see where your calculation relies on a relationship or on a value list.

--
P.S. Note that in a relationship that uses the x relational operator, the matchfields are completely irrelevant. It would have worked the same way with any pair of fields as the matchfields. Don't let my selection of SerialID mislead you into thinking this is some sort of same-record relationship - it isn't.

 

Edited January 25, 2016 by comment

[steveald]

I'm afraid my understanding of "the following" probably isn't much better than yours. I presented a problem here a while back and was given this process as a solution and I was able to make it work for me.

It sounds like I could set up any field with an x relational operator and make your solution work though.

[steveald]

On January 21, 2016 at 2:35 PM, comment said:

I am afraid I got lost in your explanation. I think you are asking how to auto-enter default values into all repetitions of a repeating field. If so, have a look at the attached file.

AutoPopulateRep.fp7

 

 

This worked.

I set up a new serial number field with an x relationship and now every new record populates the local field with the global field numbers. And any changes I make in the local field remain within that record and affect that column in the grid on the layout. All I had to do for previously existing records was Replace Field Contents for all cells in the local field. Perfect!

Thank you, comment.

Edited January 25, 2016 by steveald

[comment]

6 hours ago, steveald said:

I set up a new serial number field with an x relationship

There is no need to add any fields. You can use the existing serial number field or - as I said earlier - any other field.
You do need a new TO of the table and another relationship, if you want to keep the existing relationship as one-to-one to the same record (and I believe you want to do that, if it's used to restrict a value list using the above calculation field).

 

6 hours ago, steveald said:

All I had to do for previously existing records was Replace Field Contents for all cells in the local field.

You could have done it all at once by performing a relookup.

 

Edited January 25, 2016 by comment