Jump to content

Calculating on a Custom Sequence


This topic is 2016 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

 

Link to post
Share on other sites
  • Replies 2
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Posts

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

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.

  • Like 1
Link to post
Share on other sites

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.

Link to post
Share on other sites

This topic is 2016 days old. Please don't post here. Open a new topic instead.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

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