Field Count calculation

[flook]

Hi All

I've been trying to count the number of times a text fields content appears in a related databases field (it's a repeating field). I'm beggining to think that you just can't do it with a repeating field even if you "GetRepetition ( repeatingField ; number )" and count the number of times it occurs.

Can someone please help, I'm going mad counting the number of ways I've tried to do this.

Flook

[comment]

Not sure what you are asking.

PatternCount ( GetRepetition ( Related::RepeatingField ; 2 ) ; SearchText )

will return the number of occurrences of SearchText in the second repetition of RepeatingField, in the first related record of Related table.

If you want to count all repetitions, you need to count them individually first, then add up the results. If you want to count all related records, you'd need to make the count in the related table first, then aggregate the results in the parent table.

There may be an easier way to accomplish whatever you're trying to do - but you haven't told us what that is.

[flook]

Sorry! (first ever post, dazed/confused/etc)

I have a very simple schedule with a field that repeats 84 times (i.e 84 days/12 weeks). WithIn that fields 84 repetitions I have put text such as "JOB1", "JOB2" or "JOB3" to show what they are booked in to do that day. In the other database I have a forecast where I want to show how much time a person is going to work on something, I wanted to do this by adding up the number of times (i.e. days) they are scheduled in to work on that project and then multiply it against their rate to show time spent.

PatternCount seemed to be going in the right direction but if it only shows the patterncount of one record how can I grow the count?

Many thanks Comment

[comment]

Why not make an individual record of of each assignment:

Date

PersonID

JobID

This table would be related to People and Jobs tables by their respective IDs. This way you could get a summary from practically any angle that you can think of.

[flook]

I'm not quiet sure how that table would look/work like a schedule. Would the Extra table hold the date or would all three elements have to be linked to this new table?

How would I count the number of times a personID/JobID occurs on a schedule.

It's probably blindingly obvious but I guess I've just been staring at it for too long.

[comment]

The schedule would be just a on-the-fly display of the base information against a chosen period of time. There are many options here - if you attach a screen shot of how your schedule looks now, we can narrow it down.

The base info is 3 tables, People, Jobs and a linking (join) table, let's call it Assignments. You can get a report from Assignments by finding the relevant info (e.g. PersonID = 5 ; Date = 6/13/2005..6/12/2005) and doing a summary.

Alternatively, or additionally, you can see the info 'live' by building a relationship from People to Assignments such as:

PersonID = PersonID

AND

gStartDate <= Date

AND

gEndDate >= Date

A calc of Count (ViewAssignments::PersonID) will return the number of times this Person has been assigned in the currently viewed period.

A similar relationship from Jobs to Assignments can count the number of times this Job has been worked on in the viewed period.

[flook]

Hi Comment

This has given me a lot to think about and I guess what I have to do is give up what I've done and just start again trying a more tailored relationship. The problem I'm having is there are many jobs and the people can be allocated to any of those projects as I've tried to exemplify in the attachment.

What I've been trying to do is create a multiple relationship which can check every field and then for that job name multiply it against there rate and then return that number to the jobs database adding up the total on the way. Just to make things even more complicated I have a morning and an afternoon repeating field on the schedule which means the person can do more than one thing a day. I tried for a long time to do the pattern count route but that was way to complicated.

Will go back to the drawing board, but appreciate your help.

schedule.pdf

[comment]

What you have is a classic many-to-many relationship. It's really no different from an invoice which can have many products, and conversely the same product can be sold on many invoices.

The solution is the same in both cases: a join table recording every instance of Invoice-to-Product. In an invoicing solution, this would be Invoice LineItems, here we need an Assignments table.

The need to display the data in a calendar fashion can be misleading: the temptation is to structure the solution to fit the display. But it needs to be done in the opposite direction: first structure the data correctly (i.e. just like an invoice), then attach a display mechanism.

Because the calendar display is just a tag-on, the underlying data structure remains solid and allows reporting on any kind of data aggregation you choose.

I am attaching a demo which happens to have a display quite similar to what you have, so perhaps you can extend it to fit your needs.

roster.fp7.zip

[flook]

Wow, thanks Comment

That's fantastic! have only just looked at it but first impressions are it's just what I needed to do. The crazy thing is I'd already had to put a very invoice table in and it had never occured to me to use the structure in that way. I guess you just can't build a database around the interface.

[jrRaid]

Comment,

thank you for your sample file.

What I like to do (and need) is having more (3 to 4 ) tasks a day a person.

Is it just duplicate the TO's ?

Would you be so kind to post a sample with several tasks ? Would be appreciated.

Thank you.

[comment]

You just need to put the assignment into a portal from the matching Assignment TO (i.e. 5 portals side-by-side).

[jrRaid]

Pardon me being novice...

Thanks for your reply, but I don't see how to accomplish that in the Calendar by People layout.

It's there that I like to see the different tasks for each individual.

[comment]

Like this?

rosterPortal.fp7.zip

[jrRaid]

Exactly, thank you.

I was very close with my own try and error.

Now, I suppose to be able to show the different tasks in hourly order, I just have to sort the portals by the timefield connected to every single task ?

Well...that's what I will try.

Thanks again Comment, appreciate.

[comment]

Yes, sorting either the portals or the relationships will show the tasks in chronological order. It will not align them against a time grid, however. That is a much more complex issue.

[jrRaid]

Thanks Comment.

Let me sit on that for a few hours/days.

To see if I can find the solution by myself.

If not...I hope you're willing to share your expertise, or point me now in the right direction....

[jrRaid]

Well...I tried the whole week and finally got it working.

Thank you Comment.

Only one drawback...

I have 3 possible hours for the 'people', 16:00, 17:30 and 19:00.

When each individual has those 3 timefields, the sorting is OK.

And it was during last week, so I was happy, thought everything was working with the sort on the relationship. Every hour/task nice in the dayportal.

But for next week, several has no 16:00... and now the 17:30 comes into the first row of the portal (instead of the second) and 19:00 comes in the second (instead of the the third).

Indeed, they records does not align with the timegrid....

I tried to add an empty record for each missing hour, but I don't think this is a solution.

Any hint how to resolve this ?

TIA

Edited August 27, 2006 by Guest

[comment]

Dummy records are the right idea, but they need to be in their own Slots table.

You are talking about a true calendar solution. As I said, this is a much more complex undertaking - not something I would recommend to a novice. With the added complexity of showing several calendars (one for each employee) at once. This means you will need a set of slots per Employee.

I'd suggest you start by reading this thread. Then download the free CC Calendar from seedcode and see how it's built.

[ckai]

: Digging up the dead a bit here but...

I was directed to this from my previous thread and this is pretty much exactly what I'm after but for the life of me, I can't get my head around sorting out the finer details.

What I'm looking for is having days running along the top (like it is in the roster example above) then showing 3 rows having AM, PM, Night (maybe Any as well).

I would like to show this on a staff layout and we simply click the corresponding day and shift the staff is available to work.

A similar layout would be used for the Client screen but it would just show the number that is available for that shift.

What I can't get my head around is how to set up the tables and table occurences so as to show the shifts types (AM, PM etc) as rows instead of the people as in the example file.

I've added the calendar fields my staff table, created an Availability table (date, staffID, shift type ID) and Shift Type table (TypeID, Name for storing AM, PM etc).

Any nudge in the right direction would be appreciated. I've already had 3 breaks in 2 hours to stop my brain from imploding : haha

[comment]

The roster file is rather simple - but I doubt it will fit your needs, because it will take at least 15 relationships to meet your requirements (5 days x 3 shifts). I believe you should use the "slots table" approach instead. This may be not as simple, and I wouldn't recommend it to beginners.

P.S.

Please do not replicate your question to other threads.

If a moderator sees this - I suggest moving these last two posts to ckai's original thread:

http://www.fmforums.com/forum/showtopic.php?tid/214756/

[ckai]

Thanks comment and sorry for combining the post. I thought it would be easier.

But I have sorted a few things out. I've just added a simple check box for all the shift types. Keep it simple ah :