Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Jamie999
 Share

Recommended Posts

Hi,

 

New Filemaker User and novice scripter - lurked a bit but never posted. I really hope this question makes sense to thew experts!

 

I have been requested to produce a report consisting of Employee Names, each with the single task assigned to them.

 

I have a large DB table ALL_TASKS where each record is a unique TASK_NUMBER, and each record has an employee assigned to it (EMPLOYEE_NM) from a value list (unique values from the field EMPLOYEE_NM). There's also a lot of other summary data assigned to tasks, like time to complete, etc. (Tasks are added sequentially all the time throughout our work day).

 

So far I:

 

Created a new empty table for my report, REPORT_SUMMARY, where each record will be an employee name, with TASK_NUMBER and some other summarized time info alongside it.

 

I want to create a new record in this REPORT_SUMMARY table for each unique EMPLOYEE_NM from the ALL_TASKS table, and then get a record for each employee using a "Show all records" from the new table.

 

I've made a script to freeze the interface, call up all the records in the ALL_TASK table, sorted by TASK_NUMBER, and then I've figured out how to make a value list of unique employee names derived from the records displayed, so I have a value list where each value corresponds to a record that I want in the REPORT_SUMMARY table.

 

But I am stuck - how do I create the records in the new table from my Value list? Is there another method to build the records for this REPORT table without using a value list?

 

Hope someone might have tried this sort of thing before.

 

thanks,

 

jamie

Link to comment
Share on other sites

Hi Jamie –

 

I'm having problems following your description, and your data model.

 

If you have a table Tasks that combines a task and an employee – then that already is your summary table. If you have a table with predefined, recurring tasks, then you probably should have this data model:

 

Employee ---- Task

 

and the “join table” EmployeeTask would be your report table.

 

If you define tasks ad hoc, then you'd only have

 

Employee --

 

You say …

 

produce a report consisting of Employee Names, each with the single task assigned to them

 

… but also:

 

Tasks are added sequentially all the time throughout our work day

 

So there probably is not “the” task for an employee – you probably mean "the current task (or tasks)” for any given employee.

 

So you probably need to do this:

 

• create a report layout based on the EmployeeTask table, with a sub-summary part sorted by task or employee, depending on the entity you want to report on

• find all active records

• sort by the sub-summary part's sort field

 

No need for a new table.

Link to comment
Share on other sites

Wow - Thanks for your reply.

 

I should have checked back in earlier but things got a little busy once I took your advice and used the summary part!

 

Your method gives me almost everything I was asked to get out of the filemaker. I used a "sub-summary part" and can now see summary information about tasks linked to employees. I got a chart of names (EMPLOYEE_NM), with a TASK_NUMBER alongside.

 

So you probably need to do this:

 

• create a report layout based on the EmployeeTask table, with a sub-summary part sorted by task or employee, depending on the entity you want to report on

• find all active records

• sort by the sub-summary part's sort field

 

No need for a new table.

 

We basically have a value list of employees, and we assign one to each record in the TASKS table, so you are correct, one employee ID can be associated with a number of Task lines.

 

My report looks a little like this:

(Column 1) EMPLOYEE_NM (i called up all records and sorted the sub-summary part by EMPLOYEE_NM)

(Column 2) Oldest TASK_NUMBER (summary field set to show minimum TASK_NUMBER per EMPLOYEE_NM)

(Column 3) Newest TASK_NUMBER (summary field set to show maximum TASK_NUMBER per EMPLOYEE_NM)

(Column 4) Number of Assigned Tasks by employee (summary field set to show count of TASK_NUMBERS per EMPLOYEE_NM)

(Column 5) Total TASK_TIME (summary field to show total of TASK_TIME per EMPLOYEE_NM)

 

Thanks for the help!

 

As you can see I am trying to show some time information specific to TASK_NUMBER, as each TASK_NUMBER has a TASK_TIME in the table. Is it possible to show specific information about a particular TASK_NUMBER on the same row as all this summary information?

 

I.E. I am showing the oldest task assigned to each employee by making a summary field which shows a minimum TASK_NUMBER. Is it possible in this case to also display specific other data about that particular oldest task (per employee)? (TASK_TIME of the minimum TASK_NUMBER?).

 

I think i am now limited to showing summary counts, minimums, maximums and totals in this part of the layout, and can't figure out how to display a summary field (minimum TASK_NUMBER) with that particular TASK_TIME beside it, and not just a summary of all the Tasks times for that employee). Would a portal be appropriate for this case, to get at the TIME information linked to the TASK_NUMBER I'm showing with the Summary field?

 

Thank you again for this help it was a lifesaver!!!!

 

jamie

Link to comment
Share on other sites

Glad to hear it helped.

 

As for your issue to show data from a specific record that is only identified by a summary attribute …

 

Try this: create a summary field ListOf for task_number; then create a calc field (result type = datatype of your primary ID field):

 

Case ( 
 employee_nm ≠ GetNthRecord ( employee_nm ; Get ( RecordNumber ) - 1 ) ; // for a leading sub-summary, calculate only in first group record; if you have a trailing s-s, use + 1
 Let ( [ 
   theNumberList = GetSummary ( sListOfTaskNumber /* the new summary field */ ; employee_nm ) ; // list if task numbers for this group only
   theMinimum = GetSummary ( sMinimumOfTaskNumber /* your pre-existing summary field */ ; employee_nm ) ; // minimum of task numbers for this group only
   indexPosition = ValueCount ( Left ( theNumberList ; Position ( ¶ & theNumberList & ¶ ; ¶ & theMinimum & ¶ ; 1 ; 1 ) ) ) // where in this list is the minimum?
 ] ; 
 GetNthRecord ( Task::id ; Get ( RecordNumber ) + indexPosition - 1 ) // get the primary ID of the record at that position within the group; needs to be modified for a trailing s-s
 )
)

 

and use this field as a match field for a self-join; now you can display any fields from that related record.

 

This assumes that your Task table has a primary ID (as every self-respecting table should).

Edited by eos
Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use