1 2 Previous Next 17 Replies Latest reply on Oct 7, 2016 5:44 AM by trainsguy

    Help.....

    trainsguy

      "ALERT Copy" is a Calculation Field with a Text Result.
      "Status" is a Text Field.
      "SeasonStart" is a Date Field.
      "LagTime" is a Number Field.
      "LWEpisode" is a Number Field.
      "Frequency" is a Number Field.
      "DayAdjust" is a Number Field.

       

      "NextAvailable" is a Calculation Field with a Date Result as follows.....

      If ( ALERT Copy = "D" or Status = "Cancelled" or Status = "NEW" or Status = "PAUSED" ; "" ; SeasonStart + LagTime + (LWEpisode * Frequency) + DayAdjust )

       

      This formula worked fine in FM Pro 4.0 which I had been using for all my database needs up until a couple months ago when I decided I wanted to update for more internet flexibility, and I started using FM Pro 11.0 Advanced.  Contrary to popular opinion, the dinosaur versions work just fine in Windows 7.  In fact, 4.0 still works just perfect in Windows 10.  But I digress.

       

      Now, I can't get the default value of "SeasonStart + LagTime + (LWEpisode * Frequency) + DayAdjust" to display when the previous conditions are false.  Instead I get the dreaded "?".

       

      If I create a test field with just the "SeasonStart + LagTime + (LWEpisode * Frequency) + DayAdjust" formula, the expected value displays fine.  It's only when it appears as the default value in "NextAvailable" that I get the "?" when the previous conditions are false.

       

      A typical computation would be.....

      Season Start = 10/01/2016
      LagTime = 7
      LWEpisode = 3
      Frequency = 7
      DayAdjust = 4

      10/01/2016 + 7 + 21 + 4 = 11/02/2016

       

      And as I said, in FM Pro 4.0, that's what I would have received if the previous 4 conditions were false.  Now, in FM Pro 11 Advanced, I get a "?".

       

      I feel like a total idiot asking this because there is no reason I shouldn't already know the answer, but can someone help me out with either something that is so simple I can't see it, or some idiosyncrasy which now exists in the newer versions.  Or maybe there was somethig wrong in FM Pro 4.0 that let me get away with something I shouldn't have been able to do.

       

      In any case, I'd appreciate anyone's help or suggestion.

        • 1. Re: Help.....
          TSPigeon

          trainsguy:

           

          I am going to move this thread from the FileMaker Community Feedback Space, which is specifically for input on the Community itself, to the Discussions Space where you should receive more views and potentially more advice on this topic!

           

          TSPigeon

          FileMaker, Inc.

          • 2. Re: Help.....
            ErikWegweiser

            Try wrapping the results, including the "" (null)  in a GetasDate( ) function.

            • 3. Re: Help.....
              philmodjunk

              What do you mean by:

               

              It's only when it appears as the default value in "NextAvailable"

              What do you mean by "default value"? Might this be an auto-entered calculation instead of a calculation field of type Calculation

              • 4. Re: Help.....
                schamblee

                I don't have FMP11 to test.  I did create a test database in FM15 and NextAvailable  calculation field displays 11/02/2016.   Verify  that each field is of the correct data type.  Also make sure the field is large enough to display the date (Make field larger).

                • 5. Re: Help.....
                  trainsguy

                  thanks for the response.

                   

                  the problem isn't the null.....

                   

                  the problem is the default value..... and the default value is what I always refer to "result 2" as since it is the value that is displayed when the preceeding condition(s) isn't/aren't satisfied.

                   

                  although just to be sure..... I tried the GetasDate as you suggested with no success, besides, as I understand it, the GetasDate function would return a Text result anyway, and my calculation field returns a Date.

                  • 6. Re: Help.....
                    trainsguy

                    thanks for the response.....

                     

                    the default value is what I always refer to the "result 2" in an "If" function since it is the value that is displayed when the preceeding condition(s) isn't/aren't satisfied.....

                     

                    none of the fields I have listed do any type of auto entry upon creation.

                    • 7. Re: Help.....
                      trainsguy

                      thanks for the response.....

                       

                      I have verified more times than I can count that each field is of the correct data type.....

                       

                      and also made sure the field is large enough to display the date......

                       

                      not to toot a horn..... but I've been around Filemaker since when Claris first put it out..... and I know it inside out which is why this is driving me nuts because there is no reason it doesn't work like it should..... and as I said ion my original post..... did in 4.0.....

                       

                      even though I've used FM Pro 4.0 for years and years..... there really hasn't been that much changed besides cosmetics..... and of course the addition of internet capability and SQL.....

                       

                      I thought it was something that got jumbled when I did the file conversions as prescribed by Filemaker..... but that's not it at all..... if I create a test field with just..... SeasonStart + LagTime + (LWEpisode * Frequency) + DayAdjust..... it calculates fine..... but the minute I use that calculation as the default value..... result 2..... in an If function..... it gives me a ?......

                       

                      and yes..... I've tried extra parenthesis this way..... (SeasonStart + LagTime + (LWEpisode * Frequency) + DayAdjust).....

                       

                      and extra parenthesis this way..... ((SeasonStart + LagTime + (LWEpisode * Frequency)) + DayAdjust).....

                       

                      nothing seems to make a difference.....

                      • 8. Re: Help.....
                        philmodjunk

                        I've exactly reproduced your calculation in FMP 15 and it produces exactly the expected result. No question mark produced.

                         

                        Please click into the field and see if you then see a value. If so, then you simply have a field that is too narrow to display your results.

                         

                        Result 1, on the other hand, could produce a ? id you accidentally put a space between the quotes to return a space--which isn't a valid date result.

                        • 9. Re: Help.....
                          schamblee

                          I was not stating anything about your ability.   I can not duplicate your results.   I created a test database with  the fields you listed above.  I keyed in the sample data and I got the correct results.  If one of the conditions is met then the field is blank, if the conditions are not met then a date is displayed (11/02/2016).  

                           

                          If you have a sample file, I will be happy to look at.

                          • 10. Re: Help.....
                            trainsguy

                            I wasn't being critical of you..... I was being critical of me for having to ask this question that shouldn't have to be asked..... and I only referred to my background with Claris and Filemaker for my own benefit..... sort of a preaching to my own choir.....

                             

                            I appreciate any and all responses..... so please take nothing I say as being critical of anyone who is donating their time to my quest.....

                             

                            and now I just realized what I hadn't after your first response.....

                             

                            if what I understand your current reply to say is..... you have duplicated my specs as listed..... and you've got the intended result which I can't seem to get..... because what you describe is the way it's supposed to work.....

                             

                            there has to be something sitting right in front of me that I'm not seeing..... and I thank you for verifying my calculations.

                            • 11. Re: Help.....
                              trainsguy

                              and the same appreciation goes to you as well..... the field is more than wide enough to accept the date.....

                               

                              there are no spaces in between the "".....

                               

                              that makes two of you that got what I can't seem to make happen in FM Pro 11.0 Advanced but has worked for years in FM Pro 4.0.....

                               

                              I thought that something had changed as far as using the "or"..... or maybe using certain combinations of field types in nested If functions..... or something..... anything..... which would be common knowledge among those who frequent this community..... but I guess that was incorrect.....

                               

                              it should be working and for some reason..... even though the two of you get the intended results..... it isn't for me.

                              • 12. Re: Help.....
                                schamblee

                                Create a brand new test database with the information you listed above and see if you get the correct results.  

                                 

                                I have work on a project for several days and over look something.  I usually take a break and come back to it with fresh eyes, then I usually find my mistake.

                                • 13. Re: Help.....
                                  philmodjunk

                                  Ditto Schamblee

                                   

                                  If you have Filemaker Advanced, you can also put your calculation into the data viewer, you'll have to edit it a bit to include the fully qualified data references (have to add the table occurrence names), but then you can test your calculation and if you get the ? in your data viewer, you can start breaking down the calculation to check the values of each part of the expression and each field used to produce that result.

                                  • 14. Re: Help.....
                                    skywillmott

                                    For you to try out... attached here is a file I made in FileMaker 11 with the fields as you suggest...

                                     

                                    I added a text field called 'SetAlertCopyToThis', which you can type whatever into to get the 'Alert copy' calculation to provide a text result, including "D' which results in an empty NextAvailable result as intended.

                                     

                                    Also a popup menu for Status with all your options (that would result in NextAvailable being empty) plus ''RUNNING' which does result in NextAvailable showing a date as intended.

                                     

                                    Hope this helps if you can compare to your file....

                                    1 2 Previous Next