Calculating on a Custom Sequence

[Josh Ormond]

We have a calculation we use for calculating Issue numbers for a print publication. It's one of the very first calculations I ever made in FileMaker. Back in 2006-ish. Long story short, I am in the process of building a new file from the ground up, and came across this old calculation...and thought there must be a more elegant or geek-tastic way to do it. So I was hoping I could give you the basics of the problem and see how you would approach it.

 

For me this is partly an exercise to solve a problem, and mostly an exercise in learning.

 

The Details

• Issue Numbers are typically 4 digits long ( at least until the year 2086...to which I assume we won't be printing the publication the same way )
• Digits 1 and 2 = Volume Number = Number of the year the publication has been produced. ( example 2015 is year 29 )
• Digits 3 and 4 = Issue Number = Number of the issue for that year, 01 - 26. There are 26 issues each year.

 

Examples

Sample of actual issue numbers: 2820, 2821, 2822, 2823, 2824, 2825, 2826, 2901, 2902, 2903, etc

 

Problem

For many reasons we need to either calculate the difference between 2 issues, or determine the end issue after adding x issues to a starting issue, and so on.

• For example:
  • 2801 + 30 issues = 2905
  • 2905 - 2801 = 30 issues
  • 2905 - 30 issues = 2801

 

To this point most of the math has been done by parsing out the Volume element and the Issue element of the full number and doing math on them separately. This however can be messy sometimes because of what we are doing with the issue numbers. And most of the time doesn't feel very intuitive.

 

That's where I'm at. Working through a calculation(s) that gives me the answer from the examples above. Any thoughts and explanations of your calculation would be appreciated.

 

[comment]

To add N issues to IssueNumber =

Let (
serial = 26 * Div ( IssueNumber ; 100 )  + Mod ( IssueNumber ; 100 ) + N - 1
;
100 * Div ( serial ; 26 ) + Mod ( serial ; 26 ) + 1
)

To subtract N issues from IssueNumber, , make N a negative number and use the same formula above.

 

 

To subtract two IssueNumbers =

26 * ( Div ( IssueNumber1 ; 100 ) - Div ( IssueNumber2 ; 100 ) ) + Mod ( IssueNumber1 ; 100 ) - Mod ( IssueNumber2 ; 100 )

Note: your practice is breaking the "one fact per field" rule.  Ideally, IssueNumber would be a serial number, counting all issues consecutively from 1. That way you would have no problems calculating the difference between two serial numbers, or adding N issues to a serial number. Then you would only need to convert the serial number to your format, which is easy to do - as shown at the end of the first calculation above.

[Josh Ormond]

Noted. And truly appreciated.

 

In the actual database I have a completely separate serial number. There are a few odd scenarios where I can't carry the serial number over to where the math is being performed...or more accurately, in many cases, some calculation that is happening outside of FileMaker. So I was looking to better understand the mathematical approach if I have to covert the numbers back and forth.

 

Note: your practice is breaking the "one fact per field" rule.  Ideally, IssueNumber would be a serial number, counting all issues consecutively from 1. That way you would have no problems calculating the difference between two serial numbers, or adding N issues to a serial number. Then you would only need to convert the serial number to your format, which is easy to do - as shown at the end of the first calculation above.

 

I had gone through a few versions of how to calculate these, but most of the broke at a certain point. Plus if I understand the math better, I have a few other scenarios that the basic principles will likely help with.