4 Replies Latest reply on Jun 4, 2014 4:05 PM by philmodjunk

    ExecuteSQL results are repeating in the same field

    AlexE

      Title

      ExecuteSQL results are repeating in the same field

      Post

           Hi, 

           I have a layout that has multiple SQL calculations that all work as expected. 

           However, I'm running into an issue that's totally confusing me as I have no idea why and how to fix it. 

           I have 4 ExecuteSQL statements that properly calculate and display the results. I wanted to get an average of the 4 so I tried the AVG function and also tried to add all 4 and divide them by 4. However my results were clearly wrong. So I simplified everything and started by copying over the exact SQL query from one of the fields (Result_WK1) which worked fine. 

           However, if I say that the new field (lets call it Results_AVG) is equal Result_WK1 in the calculation area then I get the following issue: 

           eg: expected result is: 47 but the field shows a repeating 47: 4747474747474747474744747474

           if my expected result is 128 the field shows; 128128128128128128128128

           I've checked my field type and wether I set it as as entered, general, text, decimal... I get the same problem. 

           Any idea what would cause this?

           Thanks.

           Alex 

            

           EDIT: if I just set Results_AVG as equal to Results_WK1 and the calculation result as Text then it displays the field properly and doesn't repeat. However if I try and calculation (even simple like Results_AVG = Results_WK1 + Results_WK2 it gives me the repeating field problem. 

        • 1. Re: ExecuteSQL results are repeating in the same field
          philmodjunk

               Can you post the actual calculations you've defined using the ExecuteSQL function?

               Do these functions return a single value or multiple values separated by a record separator?

          • 2. Re: ExecuteSQL results are repeating in the same field
            AlexE
            Here is one of the working functions: 
            
            ExecuteSQL ( 
            
            "
            SELECT FLWK1_SUM
            FROM \"SKU DATA\"
            WHERE \"SKU DATA\".SKU = ?
            AND \"SKU DATA\".\"CHN\" = ?
            "
            ;"";"" ; SKU LIST::SKU ; "FS" )

                  

                 I get an expected result of a single value. I did make an edit above while you replied. If I just reference the field and set the calculation result as text then it displays a single non-repeating value. if I set it to number, that's when I get the repeating value. If I set as text and try and do a calculation, then it gives me the repeating value again. 

                  

                 I have four of these fields with the above calculation and I want to get the average of all 4 results. 

            • 3. Re: ExecuteSQL results are repeating in the same field
              AlexE

                   Another thing: The FLWK1_SUM is a summary field (SUM). I have a feeling my problem is because of that... 

              • 4. Re: ExecuteSQL results are repeating in the same field
                philmodjunk

                     I'd be concerned that your summary field might not return the total you expect, but I wouldn't expect this kind of result here. The value returned by a summary field depends on the context in which it is referenced and all such "context" is missing from an SQL query.

                     Let me see make sure that I understand the problem fully:

                     You have 4 calculation fields (Stored or unstored?). I'll name them SQL1, SQL2, SQL3, SQL4.

                     When you use Average ( SQL1; SQL2 ; SQL3 ; SQL4 )

                     you get the correct result, but repeated multiple times.

                     Hmmm, but you indicated that the name of your function was AVG, not Average. Such a function does not exist in FileMaker. Is this a custom function or an SQL function?