1 Reply Latest reply on Oct 23, 2014 5:36 PM by FentonJones

    Total No. of records expiring for the present month

    karen_joy

      Title

      Total No. of records expiring for the present month

      Post

      Hi All,

      I'm very new to the forum, I'm a Lab Pharmaceutical technician and we purchased Lab reagent for our Projects. we purchased a filemaker to monitor the expiration date of our reagent.

      Almost everyday I'm reading the forum and browsing the net, I found some that is suited to our needs, but there is one thing that I cant make it work, to display all the total numbers of our reagent that is expiring for the present month and also to display their names using portal via relationship. (see tab for expiring next month on the Dashboard)

      Please find attached link of my work. the User Name is Admin no password. 

      Thank you so much in advance.

      Karen Joy

       

       

        • 1. Re: Total No. of records expiring for the present month
          FentonJones

          FileMaker's later versions (many years) can help with a "date". Which is that if your calculation uses a date, to make it later (or earlier), it will help setting the correct month, and year, for that calculation. I think you've seen that (likely all newbies take it for granted :-). I'm just saying it as it happens in this calculation.

          The result in trying for with this calculation is do give me the Year & Month (only, not a date). It is a number Type of result. Because of this it adds a "0" to the month number, if needed; i.e., next month is: 201412; next month after that will be 201501 (first month of next year). [ It automatically use 1 for the month, and one more for the year.]

          If you have a calculation like the above for one side of a relationship, and similar calculation in the target table, you can easily match on a month (with just equals "=", not needing <> on (multiple) dates. [ It's also useful for a scripted Find on a month.]

          Let ( [
          d = Get(CurrentDate);
          // d = GetAsDate ("12/1/2014"); // test for next year
          date_next_month = Date ( Month (d) + 1; 1; Year (d) );
          month_next =  Right ( "0" & Month (date_next_month); 2);
          year_next = Year ( date_next_month )
           ];
          year_next & month_next
          )

          [ The 3rd line above, "// d =" etc., does not run. It is their so I (or you) can tern it one once, in order to see that the above (without it) will return the correct result next month (12).]

          P.S. This is the way I do it. Others may use date results for such relationships, etc.; both work, but I find the above simpler (to my brain).