Is this a bug?

[TrulySimple]

Hi All,

Perhaps I am missing something. I have a Formula that was working fine and now the result is "?", except when the 1st If statement is true. I cannot see anything that could have changed in the data schema that would effect this calculation.

Let ( [

Cost = ITM_Item::Cost ;

Fr = "Cost ≥ g_SlideScaleFrom_" ;

To = "Cost ≤ g_SlideScaleTo_" ;

result = "Cost / (1- g_SlideScale%_" ;

% = Cost / ( 1- ITM_Item::gMarkUp )

] ;

Round (

If ( not IsEmpty ( ITM_Item::MarkUp_@_RecordLevel ) ; Cost / ( 1 - ITM_Item::MarkUp_@_RecordLevel ) ;

Case ( PatternCount ( ITM_Item::gMarkUpChoice ; "Percent" ) ; %;

Evaluate ( Fr & 1 & " and " & To & 1 ) ; Evaluate ( result & 1 & ")" ) ;

Evaluate ( Fr & 2 & " and " & To & 2 ) ; Evaluate ( result & 2 & ")" ) ;

Evaluate ( Fr & 3 & " and " & To & 3 ) ; Evaluate ( result & 3 & ")" ) ;

Evaluate ( Fr & 4 & " and " & To & 4 ) ; Evaluate ( result & 4 & ")" ) ;

Evaluate ( Fr & 5 & " and " & To & 5 ) ; Evaluate ( result & 5 & ")" ) ;

0 )

) ;

2)

)

When I put it into the Data Viewer it highlights the 1st definition of the Let function and says "The specified field cannot be found." Yet if I test another simple Let function with the same "Cost = ITM_Item::Cost" it works. This is in the interface file. In the Data File the data viewer does not have an issue with the formula, but the result is still "?" except in the case of the 1st if statement it will resolve the calculation with the correct number result.

Any ideas as to why it does not accept my Let statement in the interphase file, or why it results in a "?" now when recently it worked perfect??

See screen shot attached.

%22Cost%22 is not a field!.tiff

[Vaughan]

You seem to have a let variable "Cost" that is the same a field in the ITM_Item table. This can only confuse things.

[TrulySimple]

You seem to have a let variable "Cost" that is the same a field in the ITM_Item table. This can only confuse things.

mmm, OK.

Just changed it to "C" and get the same dialog that can't find the specified field. The strange thing is this has been working for months, and I did not make any recent change to this. This is why it seems like a bug??

[comment]

A file showing the problem (preferably ONLY the problem) would be helpful.

The best way to troubleshoot this (as practically anything) is to remove parts of the calculation until the problem goes away. Especially when you use Evaluate(), it's very worthwhile to observe the calculated formula BEFORE it's passed to Evaluate(). The error you see might very well be thrown when Evaluate() tries to do its job.

I did not make any recent change to this.

No offense, but it's not very likely.

---

BTW, I have no idea what the calculation does - but perhaps it could be simplified? Evaluate() should only be used as a last resort, IMHO.

[TrulySimple]

A file showing the problem (preferably ONLY the problem) would be helpful.

The best way to troubleshoot this (as practically anything) is to remove parts of the calculation until the problem goes away. Especially when you use Evaluate(), it's very worthwhile to observe the calculated formula BEFORE it's passed to Evaluate(). The error you see might very well be thrown when Evaluate() tries to do its job.

No offense, but it's not very likely.

---

BTW, I have no idea what the calculation does - but perhaps it could be simplified? Evaluate() should only be used as a last resort, IMHO.

Thanks for your reply.

I found the problem by reviewing an earlier back up of this file where it was working. It was in fact related to the Evaluate () portion of the calc. It related to my removing the underscores in the global field names recently. So, instead of the portion of this field name "g_SlidingScale_". It should read "gSlidingScale_" to work correctly. Hence, "the field not found" reference.

What the calc does is generate the selling Price based on the Item cost field. It can be a straight % across all records, a sliding scale based on 5 tiers of From X to X with % , or an item can have its direct Record Level % or Sell price entered.

I would love to learn a simpler way if you have some ideas. : )

[comment]

I would love to learn a simpler way if you have some ideas. : )

I might have (or not) - but you need to come up with a more pedestrian explanation than that.

[TrulySimple]

I might have (or not) - but you need to come up with a more pedestrian explanation than that.

Okay lets try this:

The table is ITM_Item . This is a table with Inventory items used to support a Price Guide that puts Jobs together with parts (Items) that gives a menu pricing to field technicians.

So, in the Item table all the items have a cost at which the user company pays for each item. The calculation formula is for the Price field.

The user can apply a mark up on the cost to determine the selling price of the item. There are 3 options for this:

1. Percentage mark up - Enter a % into the gMarkUp field, select gMarkUpChoice = "Percent" from a popUp value list. This will use this percent across all the records and set the Price field to divide by the reciprocal of the % amount entered to give a true % above the cost.

2. Sliding scale mark up - Enter into the 'Sliding Scale' fields a small table of 5 levels with varying percentages to mark up the items.

Eg:

gSlideScaleFrom_1 0.00 - gSlideScaleTo_1 5.01 gSlideScale%_1 33%

gSlideScaleFrom_2 5.01 - gSlideScaleTo_2 10.00 gSlideScale%_2 40%

gSlideScaleFrom_3 10.01 - gSlideScaleTo_3 25.00 gSlideScale%_3 45%

gSlideScaleFrom_4 25.01 - gSlideScaleTo_4 50.00 gSlideScale%_4 50%

gSlideScaleFrom_5 50.01 - gSlideScaleTo_5 100,000 gSlideScale%_5 60%

All records will have a price set then by this table when gMarkUpChoice = "Sliding Scale"

3. A user may elect to have specific items marked up to a price that is not following either of the above mark up methods just by entering a % into the the MarkUp_@_RecordLevel field. This would then override the system mark up method.

Does that make sense in a 'pedestrian' language?

[comment]

It's getting closer... but I am still having trouble with #2: what exactly are "levels"? Presumably you will calculate only one price for each item - so how do you know which level to pick?

---

BTW, I believe you are confusing 'markup' with 'margin'. AFAIK:

price = cost * ( 1 + markup )

price = cost / ( 1 - margin )

[TrulySimple]

Levels are levels of the sliding scale set by the user in the example. . eg. from $1 to $5 mark up 67% ( divide by .33 ).

I learned some time ago that when marking up to use the divide by reciprocal as in "price = cost / ( 1 - margin ) " as you put it.

I think it is perhaps only nomenclature differences. The trouble is many think they are earning the % that they are "marking up" by when they use cost * Markup %. At least in the contracting world it is a big problem costing the contractors thousands of dollars. When they "mark up" ( a term contractors use ) the parts or labor or whatever they often just multiply it by the % they want to earn. But, they end up with less when they do not divide by the reciprocal as above. : )

I am happy to use the word margin, as it is really what we are after. I had thought they could be used interchangeably.

"Presumably you will calculate only one price for each item - so how do you know which level to pick?"

Let ( [

C = ITM_ITEM::Cost ;

Fr = "Cost ≥ gSlideScaleFrom_" ;

To = "Cost ≤ gSlideScaleTo_" ;

result = "Cost / (1- gSlideScale%_" ;

% = C / ( 1- ITM_ITEM::gMarkUp )

] ;

Round (

If ( not IsEmpty ( ITM_ITEM::MarkUp_@_RecordLevel ) ; C / ( 1 - ITM_ITEM::MarkUp_@_RecordLevel ) ;

Case ( PatternCount ( ITM_ITEM::gMarkUpChoice ; "Percent" ) ; %;

Evaluate ( Fr & 1 & " and " & To & 1 ) ; Evaluate ( result & 1 & ")" ) ;

Evaluate ( Fr & 2 & " and " & To & 2 ) ; Evaluate ( result & 2 & ")" ) ;

Evaluate ( Fr & 3 & " and " & To & 3 ) ; Evaluate ( result & 3 & ")" ) ;

Evaluate ( Fr & 4 & " and " & To & 4 ) ; Evaluate ( result & 4 & ")" ) ;

Evaluate ( Fr & 5 & " and " & To & 5 ) ; Evaluate ( result & 5 & ")" ) ;

0 )

) ;

2)

)

Yes, there is one price for each item. The Case () statement determines the correct margin for the item by checking

PatternCount ( ITM_ITEM::gMarkUpChoice ; "Percent" ) Then it is using Evaluating "Cost ≥ gSlideScaleFrom_<1> and "Cost ≤ gSlideScaleTo_<1>" // If it meets the criteria : result = "Cost / (1- gSlideScale%_<1>" ie. <1>, <2>, <3>, <4>, and <5> for the referenced fields. In other words there are 15 fields referenced to create the margin table.

then it just goes through to the last <5> ( the 5th 'Level' or last row of the margin table )

Make sense?

I was thinking there could be a way with less text to get the result. I do need a place for the user to enter there own sliding scale for the margin table. Meaning the From - To - % format for the sliding scale. It could perhaps be expanded to more than 5 lines, though this has served in the past without any user requests to modify.

[TrulySimple]

I don't know if it helps, but as a matter of reference this was the original version of this calc from a FM3 file when I first did this many a year ago.

The field names are slightly different then and it appears it is also before I learned what the Case () function was.

Round(If(PatternCount(Mark Up Method; "Percent"); ItemCost/(1- Mark Up);

If(GetAsNumber(ItemCost) <= GetAsNumber(Slide1a) ; ItemCost/Slide1%;

If(GetAsNumber(ItemCost) <= GetAsNumber(Slide2a); ItemCost/Slide2%;

If(GetAsNumber(ItemCost) <= GetAsNumber(Slide3a);ItemCost/Slide3%;

If(GetAsNumber(ItemCost) <= GetAsNumber(Slide4a);ItemCost/Slide4%;

If(GetAsNumber(ItemCost) <= GetAsNumber(Slide5a); ItemCost/Slide5%;0))))))

;2)

[Vaughan]

Round( Case( 



PatternCount( Mark Up Method ; "Percent" ) ; ItemCost/( 1- Mark Up ) ; 

 

ItemCost <= Slide1a ; ItemCost/Slide1% ; 



ItemCost <= Slide2a ; ItemCost/Slide2% ; 



ItemCost <= Slide3a ; ItemCost/Slide3% ; 



ItemCost <= Slide4a ; ItemCost/Slide4% ; 



ItemCost <= Slide5a ; ItemCost/Slide5% ; 



0



) ; 2 ) 

I took the liberty to clean up the "old" calc and paste it back here. Can you explain what's wrong with this (other than the field names), because this is significantly easier to understand and is more robust than the calc with the evaluate functions.

I'm thinking that the "slide" values should be in a related table to prices, linked via a relationship that does the working out automagically.

[TrulySimple]

Hey Vaughan,

Thanks for your reply. Yes, I see your point. I would need to add the additional If () statement back in as follows:

Round( If ( not IsEmpty ( ITM_ITEM::MarkUp_@_RecordLevel ) ; C / ( 1 - ITM_ITEM::MarkUp_@_RecordLevel ) ;

Case(

PatternCount( Mark Up Method ; "Percent" ) ; ItemCost/( 1- Mark Up ) ;

ItemCost <= Slide1a ; ItemCost/Slide1% ;

ItemCost <= Slide2a ; ItemCost/Slide2% ;

ItemCost <= Slide3a ; ItemCost/Slide3% ;

ItemCost <= Slide4a ; ItemCost/Slide4% ;

ItemCost <= Slide5a ; ItemCost/Slide5% ;

0

) ; 2 )

Thought the actual calculation would also contain the Table names in front of the fields being a separation model so it does get a bit longer and more confusing looking.

"I'm thinking that the "slide" values should be in a related table to prices, linked via a relationship that does the working out automagically. "

So, you mean it would be a table with say 5 or a few more if desired records. Each record contains: SlideFrom, SlideTo, and %MarkUp? So, it would be related by Slide_ID? I am sorta kinda getting it. How do you see this relationship from the ITEM table to the SLIDE table? And then it would 'automagically' reduce the 5 lines of code to 1??

[comment]

I'm still not clear on the "levels" thing. In what units are these levels? If Vaughan's interpretation is correct, then (using your example from post #7) an item costing $50 would be priced at $100 (should be $75, I think) - but an item costing $150,000 would be priced at cost (and produce a 'divide by zero' error in Vaughan's version)?!

It would also help knowing how these prices are going to be used, because - global or related - they are going to be unstored.

the actual calculation would also contain the Table names in front of the fields being a separation model

Huh? Run that one again?

[Vaughan]

I would need to add the additional If () statement back in...

No, you do not need the IF. Just put the other expression first in the Case.

BTW I have absolutely no idea what the levels thing is all about. Your explanations have not helped at all. Is is some way of calculating an item's retail price based on a formula?

[TrulySimple]

No, you do not need the IF. Just put the other expression first in the Case.

BTW I have absolutely no idea what the levels thing is all about. Your explanations have not helped at all. Is is some way of calculating an item's retail price based on a formula?

Yes, you are correct on the Case adding the statement at the beginning.

Forget the word 'levels'. This is a formula to calculate a retail selling price marked up from and item cost. Either as a straight % margin across all records or with a sliding scale margin. It is very simple. any thoughts on the relationship you mentioned?

[Vaughan]

So just explain how the cost is calculated.

[Vaughan]

I think there are a couple of things happening here. One is that you want to offer the users multiple ways of entering the amount of markup.

Don't.

Get one way working first. Then decide whether it can be done a different way that provides benefits. Multiple input methods are confusing IMHO and don't add a lot of value to the solution, but do increase the complexity significantly.

[TrulySimple]

I'm still not clear on the "levels" thing. In what units are these levels? If Vaughan's interpretation is correct, then (using your example from post #7) an item costing $50 would be priced at $100 (should be $75, I think) - but an item costing $150,000 would be priced at cost (and produce a 'divide by zero' error in Vaughan's version)?!

It would also help knowing how these prices are going to be used, because - global or related - they are going to be unstored.

Huh? Run that one again?

From Vaughans post the price cannot be determined because you do not have any values for the referenced fields.

Forget 'levels'. This is a sliding scale table in which to mark up ( or add margin ) to create a selling price based on an items cost.

In this example posted earlier are value entries to the fields referenced in Vaughans post or mine for that matter.

"Eg:

gSlideScaleFrom_1 $0.00 gSlideScaleTo_1 $5.01 gSlideScale%_1 33%

gSlideScaleFrom_2 $5.01 gSlideScaleTo_2 $10.00 gSlideScale%_2 40%

gSlideScaleFrom_3 $10.01 gSlideScaleTo_3 $25.00 gSlideScale%_3 45%

gSlideScaleFrom_4 $25.01 gSlideScaleTo_4 $50.00 gSlideScale%_4 50% - $50 / .50 = $100

gSlideScaleFrom_5 $50.01 gSlideScaleTo_5 $100,000 gSlideScale%_5 60% - $100 / .60 = $166.67

$150,000 / .6 = $250,000. Though there are no items at this price level, and if there were, it may merit a larger scale to have a lower mark up on items in this price range.

I had described exactly what these prices are used for above. They are used in and inventory file that is used by a retail price guide that puts the retail prices via a lookup ( for storage purposes ) into individual job records that contain labor and materials.

"Huh? Run that one again?" The field names in the actual formula contain the table name preceding in this standard format: ITM_Item::Cost or as above fields would be ITM_Item::gSlideScale%_1 . So this would make the formula appear longer due to the table name preceding the field name.

:D

[TrulySimple]

I think there are a couple of things happening here. One is that you want to offer the users multiple ways of entering the amount of markup.

Don't.

Get one way working first. Then decide whether it can be done a different way that provides benefits. Multiple input methods are confusing IMHO and don't add a lot of value to the solution, but do increase the complexity significantly.

It is okay. It has been in a files distributed since 1996.

The user just sets up there mark up % on one layout with the mark up table below . If they choose to use a sliding scale for mark up. Many need to have a sliding scale with a higher mark up on the lower priced items, and lower on the higher priced items. The user selects on this same preference page a field "Mark Up Method" either with "Percent" or "Sliding Scale" Thats it.

My original post was for the error from the field name change, which is resolved. Though if there was a streamlined way of what I was doing I am grateful to learn.

[comment]

$150,000 / .6 = $250,000

I don't know how you got the ".6" value, since the value to use is undefined for any cost above $100,000. The proper way to define the levels would be like this:

FROM COST      VALUE

0              33%

5              40%

10             45%

25             50%

50             60%

I am using the term "value" here, since the formula price = cost/value fits neither "markup" nor "margin".

"Huh? Run that one again?" The field names in the actual formula contain the table name preceding in this standard format: ITM_Item::Cost or as above fields would be ITM_Item::gSlideScale%_1 .

Well, I didn't mean "run that one again" literally. I am just puzzled where exactly does this calculation live. You imply it is NOT in the same table as the items - so how does the calculation know which item to consider?

[TrulySimple]

I don't know how you got the ".6" value, since the value to use is undefined for any cost above $100,000. The proper way to define the levels would be like this:

FROM COST      VALUE

0              33%

5              40%

10             45%

25             50%

50             60%

I am using the term "value" here, since the formula price = cost/value fits neither "markup" nor "margin".

Well, I didn't mean "run that one again" literally. I am just puzzled where exactly does this calculation live. You imply it is NOT in the same table as the items - so how does the calculation know which item to consider?

So, you mean the calc. would be :

Case ( Cost => FromCost1 and < FromCost2 ; Cost/Value1 ;

Cost => FromCost2 and < FromCost3 ; Cost/Value2 ;

Cost => FromCost3 and < FromCost4 ; Cost/Value3 ;

Cost => FromCost4 and < FromCost5 ; Cost/Value4 ;

Cost => FromCost5 ; Cost/Value5 )[code]

The calculation lives in the Item table, the interface file does not have any fields. So, I was incorrect in referencing this as being why the Table name precedes the field name, but it always shows in the calculation together with the Table name.

When I started creating the separation model I wanted to have the least number of calcs in the data file. At this point it is not run by a script in the interface file, though I believe I can certainly put a trigger on the "Cost" and "FromCost" fields as referenced above to remove the calc from the data table.

It appears the FromCost and Value field could be a single repeating field rather then my creating 5 separate fields, Correct? That would simplify the data schema, and your method would eliminate the "SlideTo" field that was used to create the range in my original formula.

[comment]

So, you mean the calc. would be :

 Case ( Cost => FromCost1 and < FromCost2 ; Cost/Value1 ; 

            Cost => FromCost2 and < FromCost3 ; Cost/Value2 ; 

            Cost => FromCost3 and < FromCost4 ; Cost/Value3 ;

            Cost => FromCost4 and < FromCost5 ; Cost/Value4 ;

            Cost => FromCost5 ; Cost/Value5 )

More like:

Note this assumes all 5 tiers are filled, in ascending order..

Cost / Case ( 

Cost ≥ FromCost[5] ; Value[5] ; 

Cost ≥ FromCost[4] ; Value[4] ; 

Cost ≥ FromCost[3] ; Value[3] ; 

Cost ≥ FromCost[2] ; Value[2] ; 

Cost ≥ FromCost[1] ; Value[1] 

) 

[TrulySimple]

More like:

Cost / Case ( 

Cost ≥ FromCost[5] ; Value[5] ; 

Cost ≥ FromCost[4] ; Value[4] ; 

Cost ≥ FromCost[3] ; Value[3] ; 

Cost ≥ FromCost[2] ; Value[2] ; 

Cost ≥ FromCost[1] ; Value[1] 

) 

Note this assumes all 5 tiers are filled, in ascending order..

Yes, that is more clean. Here is how it looks setting it up to give the margin on the % Value entered ie Price = Cost / ( 1 - margin ) . Also including the 2 other Case statements at the front:

This does look more clean. Thanks for your clarity. I think I found that the table names were required when using the data viewer and some how I had started pasting from the data viewer for some of the formulas that I test there first. And it is nice to not have the table name included.

Now if I was to put this into a script from the interface file that would update a number field "Price" when an OnObjectSave or OnObjectValidate is triggered it works simple enough. Though if the global fields are modified it would need a separate script to loop through the entire ITEM table to update the records......mmmmm.

 Round( 

      Cost / ( 1 - 

      Case ( 

      not IsEmpty ( MarkUp_@_RecordLevel ) ; ( MarkUp_@_RecordLevel ) ;

      PatternCount( gMarkUpChoice ; "Percent" ) ; ( gMarkUp ) ;

      Cost ≥ gSlideFrom[5] ; gSlideValue[5] ; 

      Cost ≥ gSlideFrom[4] ; gSlideValue[4] ; 

      Cost ≥ gSlideFrom[3] ; gSlideValue[3] ; 

      Cost ≥ gSlideFrom[2] ; gSlideValue[2] ; 

      Cost ≥ gSlideFrom[1] ; gSlideValue[1] 

      ) ) ; 2

    )

[comment]

I don't know how many items you have. Since it seems you are only using this for a lookup into the jobs table, the prices could well remain unstored - unless you intend to scroll through thousands of items while displaying the price...

If you wanted to move the entire logic into a script in the UI file, then I believe it should do the work when selecting an item for a job (i.e. instead of the lookup). But then you would have no prices at all in the Items table.

[TrulySimple]

Actually the user will use the pricing as far as reviewing their inventory and pricing in various reports in the ITEM table. My previous use of this file contains about 5000 items, though another larger company could easily double or may want all of what is available in the file meaning 20 times perhaps.

Yes, the user can use several subsummary list reports that drill down into the data and the price may not be on all layouts but certainly is on some as well as on the ITEM Detail layout.

[comment]

Well, then it might be better to write the prices into the items table. I don't suppose that will happen very often anyway.

[TrulySimple]

Right. It would be rare that one changes their pricing structure on the items. Thanks for your help, in clarifying this.