AnsweredAssumed Answered

Executing script from a calculated field to generate value

Question asked by philj on Aug 22, 2018
Latest reply on Aug 24, 2018 by philj

Hi everyone,

 

This is my first request to the group so I apologize if I am overly verbose or am missing some details, so here goes:

 

How can I call a script from a calculated field to generate a value?

 

Here is context of my requirement, if it helps:

 

- Developing a database "Project" table" to track widget manufacturing processes.

- Each "Project" contains a portal/link to a "Steps" table containing a set of steps to fabricate each widget

 

For example, "Project" table contains:

- id_ProjectID- unique key field used to link to Steps table

- Customer

- Quantity

- Part Name

- DueDate

- Portal to Steps table with steps list

 

“Steps” table for each project contains:

  • "id_ProjectID" as key field
  • "StepNumber" Number field used for sorting and re-ordering steps
  • "StepDescription"
  • "Employee" field. *** This text field is empty until Employee field is entered indicating step is complete.

So at any given time, the "current" step is the step following (next higher StepNumber) the last one containing an entry in the "Employee" field.

 

The database tables and relationships and layouts are already implemented, but need to figure out a way to generate a report of all current projects, including current fabrication step of each project.

 

So the report layout for each line item would look something like:

[Customer], [Quantity], [DueDate], [StepDescription] of current step.

 

 

Currently see 3 ways to accomplish this, is one method easier than the others?

In all 3 cases, a script "FindCurrentStep" could perform the following for the Steps table:

     - Perform Find in the Steps table of all records where "Employee" field is blank  {e.g., not isValid (Steps::Employee) }

     - With this found set, sort by  "StepNumber" field { SortRecords [Restore; No dialog] }

     - Return the contents of the "StepDescription" field of the first record in the found set. { GetNthRecord (StepDescription;1) }?

 

 

Option 1: To generate the report, run a report generator script to iterate through each record in the "Project" table

     - for each project, perform the search script described above, and enter the returned value in the appropriate report field for the Project

 

Option 2: the "CurrentStep" field is stored in the Project table, as an automatically calculated field whenever the record is viewed.

     - no scripting in the report, just a layout referencing the Project::CurrentStep field

     - How to call a script to perform the "FindCurrentStep" script and return it as a calculated value for "CurrentStep" field?

 

Option 3: the "CurrentStep" is stored in the Steps table, where the "FindCurrentStep" script would be executed automatically. The report layout would reference the Steps::CurrentStep field

     - no scripting in the report, just a layout referencing the Steps::CurrentStep field for the current Project

     - Same question about executing the "FindCurrentStep" script from within the Steps table.

 

I'm still developing my scripting skills so any assistance to point me in the right direction of which option would be the best to use, and how to implement, would be greatly appreciated.

 

Thanks in Advance!

Outcomes