Forum Discussion

AdityaBhandar's avatar
AdityaBhandar
Qrew Member
3 years ago

Error for Date(formula) field

Hello, can anyone please help me with this logic?

Formula for field type: Date(forumla)
If([Match]="Y",ToDate("GetFieldValues(GetRecords("{12.EX.'" & [NUM1] & "'}",[_DBID_UPLOAD]),15)"),ToDate("Jan 30, 2000"))

Error: Expecting Datetime/workdate but found textlist

I am trying to say, if field Match="Y", then use date from UPLOAD table by matching NUM1 in details with NUM1 in Overrides.

Field ID 15 in table: UPLOAD is date.



------------------------------
Siva
------------------------------
  • PaulPeterson1's avatar
    PaulPeterson1
    Qrew Assistant Captain
    One issue I'm seeing is that your query can return more than one value and the ToDate function is only designed to work with one value.  If you are certain the query will only return 1 date, you could create a variable and us that in the ToDate function.

    var date = dateQuery = GetFieldValues(GetRecords("{12.EX.'" & [NUM1] & "'}",[_DBID_UPLOAD]),15)
    
    If([Match]="Y",ToDate($dateQuery),ToDate("Jan 30, 2000"))​


    ------------------------------
    Paul Peterson
    ------------------------------
    • AdityaBhandar's avatar
      AdityaBhandar
      Qrew Member
      Paul, how else I can retrieve the date from other table then?

      ------------------------------
      Siva
      ------------------------------
      • PaulPeterson1's avatar
        PaulPeterson1
        Qrew Assistant Captain
        Are you certain there is a 1 to 1 relationship between the two records?  If so, you could create a relationship where the Upload table is the parent and the Overrides is the child and pull the date in as a lookup.  If it is not a 1 to 1 relationship, then the issue will be much more complicated.

        ------------------------------
        Paul Peterson
        ------------------------------