Interchangable Calculation? or some such

[yafreax]

I have a solution that is basically a quoting / PO / Invoicing program. Well, what we need is to be able to put a cost (our cost) in and a mark up% or a mark up set amount and it calculate the pricing (cust. price). Easy enough... Well, some places that we order wholesale from give us their catalogs. In the catalogs are the MSRP (customer price) and a letter code that tells us how to get to the cost price. So what i need to do now is allow for entry of price and the letter code to calculate our cost and our mark up % to get to that price...

I can do each one of these INDIVIDUALLY no problems. But what we need is to be able to insert EITHER the cost OR the PRICE for any given order and it calculate the rest on the fly. Is this easy and i'm just making it difficult? or is this difficult? either way, how can i get it done?

Thanks in advance.

Jeremy

[Razumovsky]

I think this is not too simple, but you can do it with overlayed transparent fields.

You need to have 8 fields

n_cost (number field)

t_markupcode (text)

n_MSRP (number)

n_markup% (number)

c_MSRP: case(isempty(t_markupcode), n_cost+(n_cost*n_markup%), n_MSRP)

c_markup% case(isempty(t_markupcode), n_markup%, c_MarkupLookup)

c_Cost: case(isempty(t_markupcode),n_cost, n_MSRP/c_MarkupLookup)

c_MarkupLookup is a calc that returns the appropriate % for the markup code.

your layout would show:

c_Cost---t_markupcode---c_markup%---c_MSRP

n_Cost------------------n_markup%---n_MSRP

The top line is the bottom layer in the layout, with prohibit entry into field checked (except t_markupcode).

The bottom line would be exactly overlayed and transparent, allowing entry into field.

Get the idea?

[Ugo DI LUCA]

Edited Misreading. Sorry for that, but I still consider that Razumovsky has the good answer for your case.

Original post :

I do not know if you want the user to do that on a layout or have it done several times a year when the whole collection price and catalog is given to you by the wholesaler.

If first answer is what you need, then HDH gave you a good tip.

If it is the second, it will almost depend on the kind of codes used by your wholesaler, and the structure of your Product File.

I have a wholesale/retail business, and I'm doing quite the same thing using my vendors catalog price and costs. The specificity for me is that :

- I deal with 23 vendors

- They all have different methods for Price/Costs, but usually it is a % from the MRSP (but I would prefer a code)

- Products are listed by category and collections

- Costs could change according to the quantities ordered (pallet or detail) and the category.

In summary (would tell in detail if it was really your need), the calculation in the Product db is made using a record from my Vendors File, but you could also do it at the fly using relationships and portals to filter your product file till you match the correct collection (or price code).

[daltwin]

Why not just add a radio button that you can use to indicate whether it is cost or price. The in the formula that calculates the cust price, you can use:

If(Radiobutton = "Cost", put_your_formula_for_cost_calc_here, put_your_formula_for_price_calc_here)

So it will be user driven at data entry, by way of the radio button, which would be next to the field where you enter the cost or price.

[Razumovsky]

Hi Daltwin

While the radio button may seem to be simpler at first, lets think it through:

So it will be user driven at data entry, by way of the radio button, which would be next to the field where you enter the cost or price

where would the calculated cost or MSRP display? You would need create 2 seperate calc fields (one calc cost one calc MSRP, or one calc field return either and one calc field that identifies whether the calc is cost/MSRP). Then you would also need to create a calculated or relational Markup% to display the result of the manufacturer code.

Where would you put these display fields on the layout? Next to the number/text fields for cost/MSRP/Markup%? That would double the # of fields on your layout- you would probably want to overlay them anyway.

When a customer purchased an item, how would you know whether to use the c_MSRP or the n_MSRP? You would have to either script something, or define another calculation to identify the selection of the radio button.

By the time you are done, you have created more fields, done more work than the 8 field overlayed suggestion at top, and your layout would be complicated by an additional element(radio buttons), and your user would have to make an additional selection.

Putting a little extra work in at this time with the 8 field overlayed solution results in c_MSRP, c_Cost, c_Markup% that you know will always have the correct figures, regardless of whether the data was entered from cost or MSRP. This simplifies things for all other steps later on.

In this case, the simpler solution is the one that appears more complicated at the outset.

-Raz

[jasonwood]

Personally I would always want to use a script attached to a button for this.

I would never want my sell prices to change automatically just because my cost changed, especially if I have units in stock with price tags at the old price.

I have a button which checks the category and the cost, and comes up with an appropriate sell price based on the expected margin in that category of prices. If the number is high enough, it will round to the nearest 10 and subtrac 1 (to come up with prices like 189, for example). You'd simply have to add in a step that checks to see which price has been filled in, and if it's the sell price, simply go backwards to find out the cost, using a "code" field as reference for expected margin.

The other advantage of course is that you are free to change the price at any time, without having to worry about anything else changing inadvertently.

[yafreax]

First of all, thanks for all the thoughts and ideas. Not sure which one will work for a couple of reasons. 1.) Emergency's and Fire's poppin up everywhere at work, so i havent been able to work on it. 2.) i forgot to mention one fact that i realize may be pretty important to all of this...

all the input is currently done through a portal with "allow creation of records" turned on, not in the actual Pricing DB. So, i wish i could try these methods right now, but i cant (barely have the time to read and write this )... if you guys have any thoughts as to which of these methods mentioned will work and not work with my added portal prediciment or what modifications i might need to use to make these work.

I'm decent enough in filemaker to do all the things you guys are talking about. But i'm not in it enough for my brain to come up with the solutions. Thanks alot for you help thus far.

Jeremy

[Razumovsky]

Jason has a good point-

The solution I suggested is a variation of one I use for on the fly pricing of items (sometimes the salespeople give one quote for a goup of items after tax, and I use this to break up the total into qty*price*tax, or allow the sales people to enter in just the price and calculate it the normal way). You need to determine if you will always be sticking to the MSRP- if so, the calc solution is good. If not, you want the MSRP to result in a number field that you can then modify further. A script would be better (if not essential) for this.

Both should work in a portal (you would want to run the script in the file the portal is accessing).

-Raz