• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

How to relate grouped dates to a billing period.

I am designing a filemaker pro database for a trucking company (using Macintsoh G5, OS 10.4.10 and have just gone to FMP vers. 9). The trucking compasny's largest customer sends them orders daily but insists upon receiving a grouped invoice twice monthly. I have therefore divided the year into 24 half-month periods. i.e. all orders received between the 1st. and 15th. of Jan are in billing period one. From the 16th. to the 31st. of Jan = billing period two. For example: an order received between the 1st and 15th of June falls into billing period 11. If the order comes in between the 16th and 30th of june, the billing period is the 12th. December consists of the 23rd and 24th billing periods,
I have designed a small script that finds all the orders within any given billing period and totals the costs of the individual orders for an invocie total to be billed. Everything quite simple& so far.
Problem: When an order comes in, it receives a date of order in field one. How can I write a script that will return the billing period in field two? In other words, if I type (for example) the date, January 6 into field one, I need to have (automatically) 01-(current year). In other words, the first period of the current year. Another example: if the order arrives between the 16th and the 30th of june  say the 17th of June  is typed into field one, I need to have 12-2007 in field two. In other words, the 12th billing period of the current year.

Can you help me?
1 Solution
make field2 a calculated field or an auto-entered field from a calculation =
If ( Day (invoice_date) <= 15 ; (Month (invoice_date) *2) - 1 ;  Month (invoice_date) *2 )
which means multiply the invoice date month by 2, and substract 1 if invoice day <= the 15th.

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now