1 2 Previous Next 15 Replies Latest reply on Jan 25, 2016 9:16 AM by steveald

    Modifiable Repeating Field Entries?

    steveald

      I have a layout that basically creates a spreadsheet with a header row, and dozens of rows of data below it. To cut down on the number of fields that have to be created, the header row is a repeating field, as are each of the data rows. Each cell in the data row fields is calculated based on the cell in the header row directly above it. Just like you can do in Excel.

       

      This layout is available for every record in the database and, until now, the values in the header row field were always the same. So I was able to make it a global repeating field. Each existing and newly created record has the same values in the repeating field. The values in the data rows changed based on other record-specific settings.

       

      Now, I need to make it so I can enter custom values to replace any of the values in the header row. And the custom value must be limited to the record it is entered in. So, that means the header row must lose it's global standing. Effectively, what is normally 10, 20, 30, 40, 50, ... needs to be able to become 10, 15, 20, 25, 30, ... or any other scenario.

       

      This creates two issues. How do I set up the header row repeating field so it populates with the standard list of values whenever a new record is created? And how do I configure it so values entered into non-repeating custom value fields will appear in their corresponding cell in the repeating field?

       

      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.

       

      Any thoughts? Screenshot attached.

        • 1. Re: Modifiable Repeating Field Entries?
          electon

          steveald wrote:


          And how do I configure it so values entered into non-repeating custom value fields will appear in their corresponding cell in the repeating field?

          Not sure if I understand it correctly.

          There's also the Extend () function.

          Extend

          This allows to use a non-repeating field in every repetition of a repeating field.

           

          Creating something when a new record is created can be done with a script trigger OnRecordLoad:

           

          If [ Get ( RecordOpenState = 1 )  // 1 = New, uncommitted record.

          // do your stuff here

          End If

           

          Or explicitly via a script attached to a button.

          • 2. Re: Modifiable Repeating Field Entries?
            DavidJondreau

            You've probably got some issues in your structure here. I having trouble visualizing how your data is being stored.

             

            Anyway, it might be better to add a couple fields to the table, a "starting value" and an "increment value".

             

            Then your repeating calculation would be:

             

            Let( [

            rep = Get ( CalculationRepetitionNumber ) ;

            start = Extend ( table::StartingValue ) ;

            increment = Extend ( table::Increment ) ;

            result = start + increment * ( rep - 1 ) ;

            ];

            result

            )

            • 3. Re: Modifiable Repeating Field Entries?
              steveald

              Let me add a few details to see if that helps.

               

              It starts with a global field with 10 repetitions populated with 10, 20, ..., 100 we'll call g_Base Amounts. This is the field I now need to initially show those 10 values, but allow for one or more custom values to be entered - probably from a separate set of 10 fields - to replace the initial values. 10, 15, 20,... in place of 10, 20, 30,... for example.

               

              The header row you see is a repeating field with the calculation: g_Base Amounts * 1000, resulting in what you see at the top of the screenshot.

               

              Each row after that is a repeating field that follows the format: g_Base Amounts * Extend ( complex calculations  ). So, I was inaccurate in saying the calculations in these rows are based on the header row - they are based on the same field the header row is based on.

               

              I know repeating fields are unpopular, but this method keeps me from having to create 460 calculation fields for just this layout.

               

              electon, that script trigger might just work for the initial population of the g_Base Amounts field. As for your question, I think I will need to create 10 custom fields such that, as in the example I just gave, entering 15 in the second custom field will replace the 20 in the second cell in g_Base Amounts - changing the results of the header row and all the calculation rows below it.

               

              David, I think we're on the same path here. I started with a repeating field with the calculation:

               

              Let ( [

              n = Extend ( Min Amount) + 10 * ( Get ( CalculationRepetitionNumber ) - 1 )

              ] ;

              Case ( n ≤ Extend ( Max Amount ) ; n )

              )

               

              But, those values are not replaceable of course. So I tried getting the header row field to pull values from this field, but could only get the first cell to populate.

              • 4. Re: Modifiable Repeating Field Entries?
                electon

                Do you want to store the custom values somehow with a particular calculation set?

                Otherwise just use one global repeating field where user enters custom values and a separate repeating global calculation with globalField * 1000 and base the rest on that.

                 

                If you want to store the custom values then we need to think about the context / relation to the stored contents in another table.

                Is this a list view you're showing or a portal?

                 

                My idea is to have a separate table where the first record is the default set and all others are custom sets and when you arrive at your desired point load the values into the global from there.

                But that depends on what you're trying to do.

                • 5. Re: Modifiable Repeating Field Entries?
                  steveald

                  Everything I do is in Form view.

                   

                  The problem I have is this is an ever-growing database with one record per "customer". There are literally thousands of records and dozens of different layouts with this spreadsheet-like look that they all use. Each record will have unique settings that control what appears in the body of the grid, so "no two are alike". But - at least for this layout - every record had the same header row, so I could use global settings for it. Until now. Now, even the header row can be different from record to record and what appears there for one record cannot affect what appears there for another record - unless they both keep the original set of values.

                   

                  Thoughts?

                  • 6. Re: Modifiable Repeating Field Entries?
                    electon

                    I'm just thinking where you need to store the custom values and how complex the whole thing is.

                    You say one record per customer but I can see many rows of data, so these must be records as well, in a portal, right?

                     

                    If the header values must be adjusted for a customer, you need to store the header values in a table that is related to that customer. the rows of data also have a customer id field?

                     

                    Or is this a virtual list? Are you familiar with this technique?

                     

                    Take a look at this file, it's very crude but maybe you'll find something useful.

                     

                    P.S. Pay attention to the script triggers.

                    • 7. Re: Modifiable Repeating Field Entries?
                      steveald

                      I'm afraid I may be confusing you. There are no portals, no separate records for any customer. And there's no virtual list or any use of ExecuteSQL. There is just this database.

                       

                      Each record in the database is for one customer and this is their only record. Each customer is actually a company with many employees - of all different ages. The header row contains different benefit amounts the employees can purchase and the price they pay is based on the selected benefit amount and their age. Hence the large grid. And this layout is just one of many available to each customer for their employees.

                       

                      I need the database to generate an initial list of values for the header row that will be the same for every record and will stay the same as new records are created or as I go back to existing records. And I need to be able to enter a value in a single field in any record that will change the corresponding header row value as well as each of the values that appear below it in the grid. And the change cannot affect the values in the same position in any other record.

                      • 8. Re: Modifiable Repeating Field Entries?
                        electon

                        No confusion here, it's impossible to deduct from the screenshot what the data structure is.

                         

                        So, the only thing that can be modified is the header repeating field?

                        I assume the individual cells are unstored repeating calculation fields that cannot be modified by user entry.

                         

                        In the example file I uploaded it's more less exactly what it does.

                        If you modify this so on new record it always loads the defaults from header table.

                        At the moment default is the first record but you can build the logic however suits you.

                         

                        When you change any header row value it will create a new related custom set ( a copy of the current one ) in the header table and since then it will remain a custom one.

                        The reason for initially copying all header repetitions to the new header record is that it's easier to do.

                        So you don't need to muck around with separate fields.

                         

                        But if you want to only change one header cell and save that, then you can using just the repetition number.

                         

                        Do you need a master, default header record that when changed will affect all default header column calculations for all companies that use it?

                        Or can this be set at creating a new record and then no updates are needed?

                        • 9. Re: Modifiable Repeating Field Entries?
                          steveald

                          Sorry, I'm trying to wrap my head around what you are saying.

                           

                          I'd rather not have to introduce new records just for the purpose of setting up the header in one layout.

                           

                          Each record has a setup layout that allows me to enter data and select options, all of which affect what will appear in the non-bold cells in the attached grid example. Until now, the bold values across the top and down the left side were fixed - the same for every record. Now I need to be able to enter numbers on the setup screen that will affect the grid - one number changed affects one column on the grid. Take a look at the attached screen shot from the setup screen. "Benefit Amounts" is the current global repeating field that calculations for each row on the grid including the header row are based on. "Custom Benefit Amounts" are 10 individual fields that need affect the Benefit Amounts values such that, if I enter 25 in the 2nd custom benefit field, the following things happen. The corresponding cell in "Benefit Amounts" changes to 25, which means the $20,000 in the header row of the grid seen in the first screen shot changes to $25,000, and each value directly below it in the grid increases by 25% to correspond to the 25% increase from 20 to 25.

                           

                          Does that help?

                          • 10. Re: Modifiable Repeating Field Entries?
                            electon

                            Yes I understand.

                            So basically what you need to do is have a separate repeating field that you can customise deviations from the standard.

                            This is stored with the same record and you have to find a way to re-point your calculations to the custom values if they deviate from the standard.


                            This you can do based on a calculation:

                            If ( IsEmpty ( customField [repetitionNumber ] ) ; defaultField [repetitionNumber] ; customField [ repetitionNumber ] )

                             

                            I' suggesting that on RecordLoad a trigger takes care of that = overwrites the globalField repetitions weather there's a value in corresponding custom field repetition.

                             

                            This needs to be done within a loop. That is also in the example file.

                            • 11. Re: Modifiable Repeating Field Entries?
                              steveald

                              Thanks. I'll see if I can figure out how to incorporate your methods into my database.

                               

                              I see Form view is not available in your example. Will it work in Form view?

                              • 12. Re: Modifiable Repeating Field Entries?
                                electon

                                Sure it will.

                                Just treat every single record as your own customer record.

                                • 13. Re: Modifiable Repeating Field Entries?
                                  steveald

                                  Solution found via an XPOST on FMForums.

                                   

                                  I set up a serial number field with an x relationship to itself and created a local field that inherits its values from a global field with every new record created. Now, any changes I make to a cell in the local field remain within that record and the values in the corresponding column in the layout grid change accordingly. All I had to do for previously existing records was Replace Field Contents for all cells in the local field.

                                  • 14. Re: Modifiable Repeating Field Entries?
                                    electon

                                    It does not matter if it's in the same table or a separate one.

                                    The principle of operation was still the same.

                                    1 2 Previous Next