Jump to content

Recommended Posts

Jure    0

Hi all!

I'm stuck. Really stuck. I can't wrap my head around this problem. So here's the issue:

I have 4 tables:


A member logs hours worked into a project as line items. Earnings are also logged per project as line items with date and amount.

Now I need a report that would show total earnings per user per month. I know I'm missing something but I don't know what.

Please help! Thank you!



Share this post

Link to post
Share on other sites
Fitch    148

Seems like you'd first need to break down the earnings/month for each project. Then you'd need to find the total hours/month for each project. Divide one into the other to give you earnings/hour for that project for that month. You might want to store that result in a table that you can then use for lookups. Then multiply by each user's hours for that project for that month.

It's the end of the day and my brain is fried but those are my initial thoughts.

Oh, and welcome to FM Forums!

Share this post

Link to post
Share on other sites
comment    1,390
3 hours ago, Jure said:

Now I need a report that would show total earnings per user per month. I know I'm missing something but I don't know what.

From what I see, earnings are attributed to a project, not to any specific user. And any user can log any number of hours in any project - so how exactly should the earnings be divided among the users? If it's by the number of hours logged in a specific month (as Fitch seems to think), then it gets complicated.

BTW, I don't see a date field in the Hours table, so currently you have no way to determine  the number of hours logged by a user in a specific month. 

Share this post

Link to post
Share on other sites
Jure    0

Thanks guys for quick response.

Yes indeed - Number of hours is tied to project (let's say product development). Hours are only logged once - when the project is finished and product is launched.

But then earnings are perpetual, ongoing, month by month, like monthly sales from a product... Now I would like to divide this earnings among all users based on the ratio of their hours that they logged for the project (development of the product).

For example:

Tom worked 75 h on project A = 75% of total project earnings
Frank worked 25 h on project A = 25 % of project earnings

So for each month I need to basically calculate (all the earnings in that month) x (ratio of hours worked per user).  So we have many projects, many users, and many earnings... And my head explodes. :)

Share this post

Link to post
Share on other sites
comment    1,390

This is not a simple problem. You're up against a blind side of the relational model: the data is there, but it's difficult to display it in the format you require. It is difficult, because no table has enough records to produce the required report.

IMHO, the simplest solution here would be to script the allocation of earnings to users as records in a new table. You'd probably want to make this a part of creating an earnings record. Once you have such table, the report becomes trivial.

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Who Viewed the Topic

    9 members have viewed this topic:
    ryan360Works  Junior360Works  htmbolton  Lee Smith  Ron Cates  doughemi  FPW  hudi  David Wikström 
  • Similar Content

    • By captainllama
      Hello all, thanks for reading. I am using FileMaker 11.0.3, with the Business Productivity Solutions v7 (bps_Sales_Orders.fp7, bps_Inventory.fp7, bps_Contacts.fp7).

      There is included a report generating "Sales Numbers" which gives the sales value totals in £ per week, month, quarter, or year. However, I want to generate a report showing total inventory items sold. I have an inventory of 170 items and need to generate a report that shows, for example:

      October 2013:

      Part Number           Item                         Quantity Sold
      1172-2                      Rock Teeth               1422
      1172                         Wood Teeth              342
      1101                         Tooth Bolts               205
      12m                          Chip Guard              17
      44005                       Drive Belts               38
      etc etc

      While the "Sales Numbers" report just takes a single figure from each Sales Order, this "Line Items Totals" report needs to take one or many Line Items from the inventory portal in each Sales Order.

      My skill with FileMaker is such that I have adapted the bps to more or less suit my needs but not much more than that. By searching for a solution it seems that maybe there is a report wizard to ease the process.

      It would also be helpful if the same could be done by customer, ie a report showing: Customer X, in Year Y, bought 17 Widgets, 130 Thingummies, 2 Whatsits etc etc.

      I'm frustrated that this must be a fairly common need yet neither my own efforts nor searching has born fruit. Please help!
      Many many thanks, and if this would be better in a different topic please advise thank you.
    • By Joost Miltenburg
      Has anyone tried to use iReport of Jaspersoft Studio to build reports ( or even invoices ) from FileMaker data ?
    • By Steven Swallow
      I'm trying to achieve something I expected to be straightforward, but after banging my head on a brick wall for 24 hours it's time to aask for help.
      I have a fairly straightforward company table and a contact table. Related one to many as you'd expect. When browsing a layout based on the companies table, I can see all the related contacts via a portal.
      I've never really done any reporting, but I wanted to create a report layout that gives me the company details at the top, and lists the contacts underneath. I expected this to be relatively simple, but I can't get it to work.
      I have the folowing report layout based on the companies table.
      Header - cust has freetext field as a title and the date
      Sub-summary by txt_Surname (Leading) - This contains all the company address details as merge fields
      Body - contains the contact details as merge fields (name, job title, email, telephone and so on)
      Footer - page number
      Now I have tried all manner of different combinations to try and get what I want. Basing it on the contacts table, different sorting and so on. When doing test prints it often shows just one contact under a company when I know there are more, and it some cases it shows too many.
      This is the first time I've tried reporting of any sort in FileMaker, so I'm really stumped by this so any help you can give me would be appreciated...I do keep thinking about a portal for the contacts, but I can see how this would work if the portal isn't big enough to accomodate a company with a hundred contacts in it, where other companies may only have one contact.
      Any help you can give me will be greatly appreciated.
    • By Court Bowman
      Announcement: Cleveland Consulting announces CCPivot2 - Create cross-tab a pivot tables easier than ever!
      Cross tab and pivot table reports have never been easier or more powerful.

      We at Cleveland Consulting have worked hard and rebuilt the very successful CCPivot product from the ground up to completely take advantage of everything that FileMaker 12 has to offer.
      New features include:
      Clickable data links - See the data behind the report! Dynamic date management - no more need to create fields to order and group date data by month or day. It's all done automatically. Required data sets - sometimes reporting on the data that is missing is more useful than what is there.
      There is a lot more to see, check out the overview video here:

      or head over to the product page here:
      Court Bowman, CEO
      Cleveland Consulting, Inc.
      Visit us on the web at http://www.clevelandconsulting.com

Important Information

By using this site, you agree to our Terms of Use.