2 Replies Latest reply on Jul 22, 2010 11:48 AM by JonathanWhite

    Date field calculation problems



      Date field calculation problems


      Hello Everyone,

      I'm new to the forum and Filemaker.  Although I'm new to Filemaker, I've been using Lotus Approach and other DB's for many years, so I'm not a total noob.  I have a problem that's driving me nuts.

      Here are my(simplified) DB parameters: Two tables: Transactions (parent) and Lineitem(child) related through Transaction number field.  Relationship allows creation and deletion of lineitem records.  the layout uses Transactions fields with lineitem as a portal field.

      My problem is this: I need to do a calculation to either place or not place todays date in a date field in the lineitems table.  I tried using the Estimate field in the Transaction table as the conditional source in the formula: If(transactions::Estimate="X";"";Get(currentdate)).  It won't evaluate it no matter what I do.  I've tried text w/ yes?no, text w/x or blank, number format with 1 or 0.  The lineitem table ignors any data in the Transactions table.  I've even tried just placing the date from the Transactions date field as a calculation, it won't get the data.

      As a workaround I've created an estimate field in the Lineitem table.  That evaluates correctly but it will only place the date in the first record in the portal and leaves the subsequent records blank.  That dosen't work.  This has me baffled.  All of the other relational fields work fine, only the date fields have problems.  The only data manipulation that seems to work is autoentry of todays date.

      I hope that someone can tell me what I'm doing wrong or if there is a problem with the program.


        • 1. Re: Date field calculation problems

          Sounds like you have a date field with an auto-enter calculation expression that doesn't change values when you then edit the Estimate field in the transactions table. This is a known limitation of auto-entered calculations. Changes to fields in related tables will not cause the field to update.

          Define a calculation field in LineItem that returns a date type and this should work for you. The resulting value is unstored, and this can result in delays if you need to refer to this field in sort or find operations, but it should work.

          If you need this value to be stored, you'll have to set up a script trigger on the estimate field that updates this date field in LineItems.

          • 2. Re: Date field calculation problems

            Thanks Phil,

            I didn't set it up to auto enter with the calc.  It just won't eval the field.  Your soln will work so I'll have to write a script to enter the data into a stored field.

            I also found a work around, I created a new calculated field in the Transactions table and inserted it in place of the lineitem date field in the table (portal) .  This evaluates the estimate field correctly and places or dosen't place the date as needed.  This will allow me to print an estimate of charges.