Query with dynamic field names RRS feed

  • Question

  • Hello,

    I'm trying to build a query with a unique problem.  We have multiple departments.  Each record is assigned a current location and based on that location it belongs to a certain department (some departments have multiple locations).  Each department has a certain number of days to complete a task on the record.  I'm trying to calculate the number of days left on their 'ProcessTime' until they are late.

    The issue is i have a DeptStartDate field for each department that I need to use to do the calculation.  I don't want to create some super nested if statement to return the respective date field.

    Here's the fieldnames that are applicable:  ContainerID (PK), HBStartDate, CLStartDate, CRStartDate, PKStartDate, SHStartDate, DeptCode (HB, CL, CR, PK, SH), ProcessTime.

    So in layman's terms...  Today - xxStartDate + ProcessTime

    How do I pass the xxStartDate field based on DeptCode?

    Any help would be great...


    Tuesday, October 10, 2017 2:39 AM

All replies

  • What you need is a related 'child' table with ContainerID (FK), DeptCode, and StartDate fields.

    Create the relationship as one-to-many.  

    Build a little, test a little

    Tuesday, October 10, 2017 4:26 PM