Calculate Record Number

[esm]

I have a group of records that include Office, Customer and Amount. I am trying to order the records based on the amount of each customer total by Office location.

The final result will be adding an Order field that calculates the order of the records for a single office.

For Instance:

Office	     Customer	     Amount	     Order

PA 	        ABC 	           $1000	         1

PA 	        DEF 	           $900	           2

PA 	        HIJ 	             $800	           3

PA 	        KLM 	           $700	           4

PA 	        OPQ 	          $600	           5

PA 	        RST 	           $500	           6

The order field has to be able to calculate on the fly (as new customers will be added). I don't care about the sort order, only that the Order field has the correct number based on the record's amount.

Is there any way to do this in FMP.

Thanks.

[LiveOak]

If the records are sorted, Status(CurrentRecordNumber) will give you the order. The trick here is to come up with a closed form calculation that gives the order (not particularly a FM question). Once you have the calculation, the implementation should be straight forward. So, the basic question becomes: How do you calculate an order?

I've never seen this down without scripting (sort). It may not be possible.

-bd

[Ugo DI LUCA]

Live Oak is right. Status(CurrentRecordNumber) may be the answer to your question. And you will need a script when processing the new entry if you want the "order number" to be entered at the fly.

You could use the Status(CurrentRecordNumber) for a calculation + a lookup.

You'll need :

c_OfficeCustomer (= Office&"-"&Customer)

selfjoin on c_OfficeCustomer --->YourFile:c_OfficeCustomer::YourFile:c_OfficeCustomer

selfjoin on Office ---->YourFile:"Office"::YourFile:"Office"

c_sortorder (= Status(CurrentRecordNumber))

g_sorted (text) set to "YES" or "NO" by the "New entry script",

c_order (text result) = Case(g_sorted = "YES", Office&"-"&c_sortorder, "")

t_OrderPerOffice (text result) = c_order by lookup through selfjoin on c_OfficeCustomer

"New entry script" :

Go to related records(selfjoin on Office)

Sort by number (ascendant)

Setfied (g_Sorted, "YES")

Goto layout B (the layout where c_OfficeCustomer is)

Go to record (c_OfficeCustomer)

Relookup

Goto original layout.

Setfield (g_Sorted, "NO")

This would lead you to t_OrderPerOffice = PA-1, PA-2, PA3,...

If you do not need the Office Abbreviation, just define c_order and n_order as num results.

If you do not need an order per office, just forget the selfjoin on "Office" and GTRR step in the script.

Because this field is set by lookup, you will need to set the lookup to let the record unchanged when not found. Therefore, your t_OrderPerOffice will only change when processing a new entry.:

If you only use one kind of sort, change the calcs to Case(c_sorted = 1, Office&"-"&c_sortorder, "") where c_Sorted (= Status(Sort)--0 when unsort, 1 when sort, 2 when partly sorted)

Or use a looping script if you prefer

[CobaltSky]

There are a couple of different approaches by which this one can be solved dynamically, and without a script in sight.

Which approach is best depends on several considerations about the kind of rank numbering that is required to fall out (eg unbiased sequence numbers versus a competitive ranking formulation...) and also the extent to which it will update across all records with or without a screen refresh (or layout change etc) to prompt a recalc throughout the file.

A method taylored to esm's exact requirements is demonstrated in a free demo download available from my web site at:

http://www.nightwing.com.au/FileMaker/demos/GroupRanking.zip

However, should anyone be interested, an alternative approach, plus a 'variation on the theme' are also online:

http://www.nightwing.com.au/FileMaker/demos/RecordOrder.zip

http://www.nightwing.com.au/FileMaker/demos/AutoRanking.zip

With FileMaker there are always several answers!

[Ugo DI LUCA]

With FileMaker there are always several answers!

Woah! This is really true

Ray, your Multikey calc is....

I still didn't understand what the 10^1, 10^2 in your calc are for...

[CobaltSky]

Hello Ugo,

The calculation formulations which produce the multikey fields in two of the demos above have a lineage which can be traced back to Mikhail Edoshin's 'Smart Ranges' technique.

It is a range-matching technique which relies on the principle of register-shifted text sequences derived from logical intervals within a number range, which are incorporated into a compound numbering system (and which, when extrapolated separately from two sources, can be used for multikey matching). The underlying theory is explained succinctly by Mikhail on his web site, so I won't repeat it here:

http://www.onegasoft.com/tools/smartranges/

To achieve matching compound strings, components of the original string must be isolated then shifted up and down through base-ten numbering registers. The function of the 'ten-to-the-power-of' expressions within the formula is to generate appropriate base-ten divisors to down-shift and then strip (using Int(Mod( )) expressions) the elements of the input number to thus isolate a part-range identifier for inclusion in the compound string (remember that the resulting compound string is being matched as text, so leading zeros are significant).

The power to which you raise a ten equates directly to the base-ten register (eg the number of zeros in the resulting string). So for instance, to drop six registers (ie move the decimal point 6 places to the left), you can simply divide by 10^6. Then, to strip everything to the right of the decimal point in the resulting number you can use Int( ), whereas to strip to the left of a given register you can use Mod(X, 10^n).

So, in combination, Int( ), Mod( ) and 10^n are used in the Smart Ranges technique to isolate individual numerals within a number string (to then recombine them with the appropriate number of zeros for inclusion in the compound string).

Hope that answers your question?!