7 Replies Latest reply on Aug 9, 2013 4:20 PM by janslort

    Repeating fields not handling negative numbers

    janslort

      Summary

      Repeating fields not handling negative numbers

      Product

      FileMaker Pro

      Version

      11

      Operating system version

      PC

      Description of the issue

      I have 2 repeating fields in an invoice file with 6 repeats.  One field is called Quantity, it is a Number field, the other is called Description, it is a Text field. I import the records into a separate file I called "Counting" & chose to split the repeats into separate records. In order to get a count of items in the Description field, I created a sub-summary section to obtain sub-totals of each item , when sorted on the Description field. I import to my Counting file frequently to update matching records on the invoice number field creating new records if they don't match, This works well for positive numbers.

      I have returns which I enter into the same file on separate records (of course), i. e. Quantity -1,  Item "wheels".  This works well also when I import the record into the "Counting" file. When I import again updating the matching records, I duplicates itself.  It ignores the negative items completely if they appear on repeat 2 through 6.

      Steps to reproduce the problem

      I created two files.  One containing the 2 repeating fields for Quantity, and the other for Description.  Then I created the "Count" file with two (non-repeating) fields and imported the records.  Same screwy result.

      Expected result

      I expected negative quantity items to act in the same manner as the positive quantity records when imported.  BTW looking at the "All Fields" on a layout, the negative items show correctly in the individual records repeating the negative numbers the same as the way the positive numbers do.

      Actual result

      Negative values in Quantity are recognized only if in the first repeat, and ignored in subsequent repeats.  The description is the one in the first repeat with no quantity shown at all.

      Exact text of any error message(s) that appear

      no error message.

      Configuration information

      P.C. Running Windows 7

      Workaround

      possibly only to enter returns in the first of the repeating fields.

        • 1. Re: Repeating fields not handling negative numbers
          philmodjunk

               I have to wonder why you use repeating fields at all when you could use a portal and just have the indivdiual records to begin with as that would elminate the need to import any records into a different table as you would be entering data into those fields directly...

               I don't follow this part of your description:

               When I import again updating the matching records, I duplicates itself. It ignores the negative items completely if they appear on repeat 2 through 6.

               Import again into what? Your table of individual records?

               Matching on what field?

          • 2. Re: Repeating fields not handling negative numbers

            Jan Slort:

                 Thank you for the post. 

                  

                 To attempt to replicate this, I created a new file called "DATA.fp7" in FileMaker Pro 11 with 2 repeating fields, a [5] repetition text field and a [5] repetition number field. I added a single record and populated the 5 repetitions for both fields with text data, and positive/negative numbers.

                  

                 I created a blank second file called "import.fp7" with a text field and a number field. I imported from the "DATA.fp7" file into the "import.fp7" file. I used the import option "Splitting them into separate records."  

                  

                 The 5 repetitions imported into the "import.fp7" file as 5 separate records. All record information imported correctly.

                  

                 The repetitions containing negative numbers were negative in the newly created records in the "import.fp7" file. 

                  

                 When importing again to update matching records what duplicates? What field is being used as the match field? 

                  

                 Am I perhaps missing a step to replicate? Can this be reproduced in two new simple test files? 

                  

                 TSFalcon

                 FileMaker, Inc.

            • 3. Re: Repeating fields not handling negative numbers
              janslort

                   Hey thanks guys for the assist.

                   TS are you entering the negatives as -2 or -1 etc?  My match field is a third field called invoiceNo.  All data in repeats 2 - 6 gets ignored in the imported file.   I reproduced  the simple file, like you did, & got the same result. No data from repeats 2 - 6.  Phil, I can't get my head around the way you are suggesting, but I have quite a few client files I would have to change anyway. 

                   Jan

              • 4. Re: Repeating fields not handling negative numbers
                philmodjunk

                     I suggest looking at the invoices solutions that come with Filemaker 11 and 12. You've got a complex system here that is failing in a complex way where a much simpler structure completely avoids the need for this importing. This simplification of your design is well worth updating multiple client files. Using repeating fields for line items in an invoice has not been the optimum design since FileMaker first became a relational database with the release of FileMaker 3.

                     Can you describe your import in more detail?

                     Are you importing from a table of  non-repeating fields into the table with repeating fields?

                     If so, that won't work as you have found out. The non repeating fields will import into the first repetition of the repeating field and not into any of the repetitions. This has nothing to do with whether or not the values are negative.

                • 5. Re: Repeating fields not handling negative numbers
                  janslort

                       Hi Phil:

                       thanks for your continued thoughts on this. 

                       I'm importing from the file containing repeats into the file without them. 

                       Jan

                        

                  • 6. Re: Repeating fields not handling negative numbers
                    philmodjunk

                         And this is an "Import Matching" import?

                         Since all records created by the "split while importing" option will all have the same value in their match field, this isn't going to work, for postivie or negative values. The value in the first repetition will replace the value in all of the matching records.

                         I would have expected that or that the value from the last nonempty repetition would have appeared in all of them. Either way, this isn't really an option for what you are trying to do. You'd need an even more complex approach or make the structural change that I have recommended previously.

                    • 7. Re: Repeating fields not handling negative numbers
                      janslort

                           Phil:

                           i've done a delete all recs then I do a straight import.  Works just fine. Thanks for the tip. 

                           Jan