Jump to content

  •  

Photo

adding together the contents of the same field from various records


  • Please log in to reply
6 replies to this topic

#1 Sarah Jayne Davies  newbie

Sarah Jayne Davies
  • Newbies
  • PipPip
  • 2 posts
  • FM Application:11 Advance
  • Platform:Mac OS X Leopard
  • Skill Level:Novice

Posted 21 March 2012 - 09:36 AM

Hi. I have created a FM database on a friend's Mac to record invoices that I receive from one employment agency. I have fields for invoice number, staff name, no. of hours and hourly rate, milage, and the cost which is a calculation field.

Sometimes, I have up to five records with the same invoice number and date, but different work dates, hours worked etc as several days may be on one invoice.


I want to create a field that adds the end total cost of each field that shares the same invoice number but am struggling.


I would also be interested to know if it is possible to make FM stop asking for a "date paid" if an invoice has not been paid. I tried to attach a copy with false names to demonstrate but this website wouldn't allow me to.

If anybody has any advice, I would be extremely grateful.

Thanks
Sarah
  • 0

#2 Ocean West  I have an idea!

Ocean West
  • Administrators
  • 2,719 posts
  • LocationSan Diego
  • FM Application:13 Advance
  • FMGo:iPhone / iPod Touch, iPad
  • Platform:Mac OS X Mavericks
  • Skill Level:Expert
  • Certification:7, 8, 9, 10, 11, 12
  • Membership:TechNet, FileMaker Business Alliance
  • Time Online: 41d 6h 3m 8s

Posted 21 March 2012 - 09:44 AM

Automatic message


This topic has been moved from "Community ResourcesArticles, Tips, Techniques & Solutions" to "Database Schema & Business LogicCalculation Engine (Define Fields)".
  • 0
Stephen Dolenski
FM Forums.com Founder, Administrator

#3 comment  consultant

comment
  • Members
  • 23,518 posts
  • Time Online: 299d 16h 50m 2s

Posted 21 March 2012 - 10:20 AM

tried to attach a copy with false names to demonstrate but this website wouldn't allow me to.


Zip your file and try again.
  • 0

#4 Sarah Jayne Davies  newbie

Sarah Jayne Davies
  • Newbies
  • PipPip
  • 2 posts
  • FM Application:11 Advance
  • Platform:Mac OS X Leopard
  • Skill Level:Novice

Posted 26 March 2012 - 01:16 PM

HI. Thanks for your reply. I have had to make a duplicate of the database in order to create a zip file.

Thanks again
Sarah

Attached Files


  • 0

#5 comment  consultant

comment
  • Members
  • 23,518 posts
  • Time Online: 299d 16h 50m 2s

Posted 26 March 2012 - 03:53 PM

When you receive an invoice, like Invoice #1 in your file: wouldn't it be easier to enter the common data once into a table of Invoices (1 record) and then enter the three line items into a portal?
  • 0

#6 Sarah Jayne Davies  newbie

Sarah Jayne Davies
  • Newbies
  • PipPip
  • 2 posts
  • FM Application:11 Advance
  • Platform:Mac OS X Leopard
  • Skill Level:Novice

Posted 31 March 2012 - 01:26 PM

No, because one invoice may contain several days all worked by the same employee. If Mrs X works on Monday, Tuesday, Friday and Saturday in one week, all of those shifts will go onto one timesheet, and therefore one invoice with the same number as the timesheet. I then use the invoice / timesheet number as the reference when paying that invoice via Barclays online banking, making it much easier to do my end of year accounts.

When I am checking bank statements and older invoices, I can easily find the information needed by searching for the invoice number. If there was a problem on a particular day, I can also very easily see who was working.

Wouldn't having each entry from one timesheet mean that each record would have to have many more fields to accommodate what could be seven shifts? Would that be what you would do?

I have been wondered whether an IF STATEMENT could be used?
  • 0

#7 comment  consultant

comment
  • Members
  • 23,518 posts
  • Time Online: 299d 16h 50m 2s

Posted 31 March 2012 - 02:42 PM

No, because one invoice may contain several days all worked by the same employee.


That's precisely why the answer should be yes. Have a look at the attached file - hopefully it will all become clear.

Attached Files


  • 0




FMForum Advertisers