Script to narrow found set to display only unique values

[aapl]

I am using FM Pro 11. I currently have a layout where I can manually put in a "start date" and an "end date", the team members "name", and press 'run report' - the report then pops up.

I am trying to put together a script to automate this process further so it will automatically run reports at the end of each working week for each team member who worked at some point during that week.

I have a table containing records with staff clocked hours. Records have staff fields "name", "date", and a field saying what they did that day, either 'worked', 'sick', 'holiday' etc. If I search for one week of records I would get something like the following:

'Monday' 'Luke' 'Worked'

'Monday' 'John' 'Worked'

'Tuesday' 'Luke' 'Worked'

'Tuesday' 'John' 'Worked'

'Tuesday' 'Sally' 'Worked'

'Wednesday' 'Luke' 'Worked'

'Wednesday' 'John' 'Sick'

'Friday' 'Luke' 'Worked'

'Friday' 'Sally' 'Worked'

So basically I need my script to search for a set of 'one weeks worth' of staff clocked hours, narrow that by only records for people who 'worked', and then narrow to show only unique staff. That way I am hoping i will be able to have another script copy and paste each unique name to my original layout and run reports.

Based on the above example my found set would have been whittled down to display just 3 records, 'Luke, 'John' and 'Sally' because in a nut shell that is who 'worked' at some point during that week.

Hopefully what I am trying to achieve has made sense to you, and hopefully someone can help me out here.

Many thanks.

[comment]

Hopefully you have a real date instead Of "Monday"? If so, find records (in the "clocked hours" table) whose date is in the week of interest and whose type(?) is "Worked". Sort these by employee. If you use a layout with a sub-summary part by employee and no body part, you will see the report you describe.

[aapl]

Hopefully you have a real date instead Of "Monday"? If so, find records (in the "clocked hours" table) whose date is in the week of interest and whose type(?) is "Worked". Sort these by employee. If you use a layout with a sub-summary part by employee and no body part, you will see the report you describe.

The dates are real dates yes just gave week days as an example...

Maybe I was unclear, I have two different layouts from two different tables. I need to narrow down a found set to find only one instance of each employee who worked during the week. Then from that found set copy and paste each one into the other layout to generate the report.

It is the narrowing down to just one unique instance I am stuck on. I can search between a range of dates to find clocked hours for one week. i can constrain the found set to show only 'worked' records for that week. But I am left with multiple records of employees.

i.e. if john works 5 days a week and sally 2 days a week I end up with 7 records. I want to be left with just 2 records, just unique employee names, no duplicates. Then I can copy and paste them into my other layout to generate the report.

Hopefully this explains my problem more clearly.

Many thanks.

[comment]

I don't see why you need to copy and paste - or use another table for the report. What is the other table?

[Vaughan]

I hope you have a "staff" table where each worker as a unique record. I also hope that the staff clocked hours records are linked to the staff table.

If so then selecting the unique staff is as simple as performing a find on the clocked hours records, then using the Go to Related Records script step to select the related Staff records based on the found set of clocked hours records.

[aapl]

I don't see why you need to copy and paste - or use another table for the report. What is the other table?

I need to use another table because the reports are generated from a service table, but I need to work out go worked from the clocked hours table.

[aapl]

I hope you have a "staff" table where each worker as a unique record. I also hope that the staff clocked hours records are linked to the staff table.

If so then selecting the unique staff is as simple as performing a find on the clocked hours records, then using the Go to Related Records script step to select the related Staff records based on the found set of clocked hours records.

You are right this has gotten me thinking. I have a staff profile table and a clocked hours table. The staff profile table has just one record for each employee. If I link them with a relationship saying names are equal, within a specified date and did they 'work' I could have a calculation field to tell me if they worked or not.

Hopefully this will work, I will give it a try and see!

Thank you for your help so far guys.

[aapl]

Ok so I have managed to fix it work work as I said above.

My next question is how can I set up a script to copy and paste field values from a found set to another layout.

I have my found set with 4 employees in for example. I need a script to copy the employees name, go to report layout, paste into report layout and run report. BUT then go back to the found set and do the same for the second record, then the third, and to know when to end when it gets to the last record in the found set, i.e. number four.

Is there a way to loop or repeat based on the number of found records and then exit script?

Has anyone got an idea of the best way of doing this?

Again hopefully what I am trying to do makes sense,

Many thanks.

[comment]

The staff profile table has just one record for each employee. If I link them with a relationship saying names are equal, within a specified date and did they 'work' I could have a calculation field to tell me if they worked or not.

If you have a relationship matching on employee*, you can search the Employees table, entering criteria into the related fields.

(*) It would be better to match on a unique EmployeeID rather than a name.

I need to use another table because the reports are generated from a service table

Can you explain what exactly is the "service table" and what is included in your report? As a rule, it should not be necessary to duplicate data you already have.

[aapl]

The employee names will always be unique as the database is for a hair salon, and we would never have 2 employees with the same name working at once. We would alway change someone's name. May sound odd but common practice in hairdressing.

I now have a working calculation which says whether the employee worked or not during the specified week.

The next one im stuck on is a script to copy a field from the first record of a found set, paste into a different layout, then go back to the set, copy the second record and paste into different layout... And know when to stop when it gets to the last record.

[comment]

The next one im stuck on is a script to copy a field from the first record of a found set, paste into a different layout, then go back to the set, copy the second record and paste into different layout...

Yes, you keep saying that - and I keep trying to dissuade you from doing that. I would gladly suggest an alternative, but I can't do that without knowing what are you really trying to achieve.

we would never have 2 employees with the same name working at once.

But you could have a former employee with the same name as a current one? It's a matter of good practice: always base your relationships on a meaningless unique ID - an auto-entered serial number works best for this.

[aapl]

Yes, you keep saying that - and I keep trying to dissuade you from doing that. I would gladly suggest an alternative, but I can't do that without knowing what are you really trying to achieve.

But you could have a former employee with the same name as a current one? It's a matter of good practice: always base your relationships on a meaningless unique ID - an auto-entered serial number works best for this.

I understand what you are saying about best practice with the employees names. Each employee, as does each of our clients, has a unique profile ID. I thought explaining it as 'staff' members would make it easier for people to understand what I am trying to do.

Hopefully I can explain to you what I am trying to achieve. I have a 'services' table which contains all of the services our clients purchase. I have a script set up already allowing me to input 2 dates, a start date and an end date of records I want to report and a field for the employees name. At the end of each week I manually put the dates in for the week just gone, and the employees name and generate the report. I do this for every employee who worked that week.

I would like this to be automated for me, I am currently able to have a found set of employees narrowed down displaying only those who worked in whichever week, but in a different table. In the 'clocked hours' table. I now just need a script which will copy and paste from that found set into the 'employee' field for me to generate a report for each employee who worked.

Can this be done?

[comment]

If I understand correctly, the Services table has fields for the date of the service and the employee who performed the service?

[aapl]

Solved this now. Thanks you your input guys, much appreciated!