Look up based on case or each

[Charity]

Ok. I understand relations sort of but I don't understand look ups. I have two questions regarding my new file. if you would be so kind, please look at this file on the orders page and explain why the dollars fill in even if the quantity is empty.

Second and main reason for post. In Products I will put two prices (each and case) and then case quantity (says whether 12 or 24 to case). Cases are cheaper. Boss will occasionally enter discount on a product which will run until he removes it. Look up on the discount and each price works.

When someone orders 14 of a product system should enter 1 case and 2 each so they get the best price automatically. I need script right? Can't figure how lookup can do that. I don't have two Qty fields in my lines table either. Do I need one? I hope note.

And then when customer gets invoice, I want it to show on line below product description that they saved $ amount by ordering case quantities. Boss said it should be easy. Boss isn't staring at what I'm staring at.

I am on Windows 7 with FM 11. Other pointers on my file always invited. Thank you. Oh. And I'm new to this but I will work hard.

Delco.zip

[imoree]

2 ideas.

calculation field = price * qty

discount can be accounted for after thats figured out.

i dont know if this is simple but

If ( IsEmpty( Discount Percent) ; Quantity * Price ; Price -( Quantity * Price * (Discount Percent /100)) )

2 ideas.

calculation field = price * qty

discount can be accounted for after thats figured out.

i dont know if this is simple but

 If (  IsEmpty( Discount Percent) ; Quantity * Price ; Price -( Quantity * Price * (Discount Percent /100)) )

[Fitch]

...explain why the dollars fill in even if the quantity is empty.

For the same reason the price doesn't change when you enter a quantity: your existing Total calculation doesn't reference the Qty field at all.

When someone orders 14 of a product system should enter 1 case and 2 each so they get the best price automatically. I need script right? Can't figure how lookup can do that. I don't have two Qty fields in my lines table either. Do I need one? I hope note.

And then when customer gets invoice, I want it to show on line below product description that they saved $ amount by ordering case quantities.

I think both the line item prices and the discount display are feasible with auto-entry calculations (rather than lookups). Scripting is always an option too.

If you want to show the customer how they saved money, you may want to make a couple more fields in Order Lines: Case Qty, Case Price, Case Total, Single Total. Your existing total would then add up the Case and Single totals and apply the discount.

[comment]

When someone orders 14 of a product system should enter 1 case and 2 each so they get the best price automatically. I need script right?

Wrong. However, you do need to lookup (i.e. copy) ALL the necessary data about the product into the line item. "Necessary" in this context means: affects the price AND is liable to change in the future.

[Charity]

So I applied everything you all said I hope. Whew@! Some fields I made formulas and some I made look up and one auto enter keeping in mind if they were necessary affecting the prices. I needed to add an Entered Qty where Cindi can type total eaches and then computer splits them. I couldn't think how else to do that.

One problem on savings, if I change the product, it doesn't change nor does the grand total. Look ups change but auto enter doesn't? Do I still have it wrong? Or if I make a mistake on the product, do I then need script to erase all the values and recompute them? Trigger, right? This stuff isn't as easy as it looks. I know you all are busy so I thank you all for your time.

Charity

Delco.zip

[comment]

I believe your Savings field should be a Calculation field. Other than that you should be fine.

---

One more thing: in the discount lookup, disable the "Don't copy contents if empty" option.

Edited September 2, 2011 by comment

[Charity]

Ah. Entered Qty * Ea Price - Sub Total

first two fields are lookup data and the third is also based upon lookup. None change so Savings couldn't change. When put as calculation, it all wonderfully updates now.

I have to laugh. 'Don't copy contents if empty' sounds like, well, no, because it would be copying nothing anyway but does a checkbox really have to ask that? Trying checked or not, I can see no diff so I would be curious as to the meaning of this strange checkbox. I got no relief from help which says

"Prevent null (empty) data in the lookup source field from being copied to the lookup destination field. Select Don't copy contents if empty. (Clear this option to allow empty data to be copied.)"

I understand difference between null and a field having a value which is then removed but I thought FileMaker did not distinguish. And regardless, the message still makes me laugh. Ah, yes, please , allow empty data to be copied. I am sure there must be a logic on why that checkbox even exists and particularly why you mention it. Please tell because File Maker isn't making it very clear to me.

As an aside, I see now that I was misspelling lookup as look up. And thank you.

[Vaughan]

Say the original value in a lookup field was 30. The master record gets changed to empty. When the field is re-looked-up, you might want the new value (empty) to replace the current value 30. Otherwise the 30 will be left there and appears to not be updating.

[Charity]

Thank you for explaining it. If you import into a field it replaces the data regardless since the purpose of the import to replicate the source field. Lookup should respect that same logic.

So thank you again Comment for mentioning it because I would have left it default enabled which means that if I changed a Product and the original had a Discount and the new one didn't then it would not have blanked the prior value from Discount. Very bad and even trying it after you mentioned it I didn't notice. I can't imagine a time when I would want that enabled but I suppose it gives options to people. Shouldn't the default be disabled then so that it meets expected behavior?

[Vaughan]

Lookup should respect that same logic.

Now you're showing that you don't understand the lookup logic, and the potential that it offers.

[Charity]

You are right. As a Programmer, I know too little about this program to be making any judgements about what it should or should not do (yet).

However, I am a User as well and as such, I have the right, no the obligation, to make my opinions known . As a User, Default should be the expected and/or less harmful setting. At the least, Help should give a simple example as you gave or re-word the paragraph.

Anyway, thank you both for helping me through this. On to other quandaries.

Charity

[Vaughan]

As a User, Default should be the expected and/or less harmful setting.

A "user" would never see this option, only developers.

The default is NOT to replace the contents if the value is empty. Isn't this the safest setting?

[comment]

Isn't this the safest setting?

I believe the point could be argued both ways.

OTOH, there can be no argument that a check box marked "Don't copy … " or "Do not replace …" is bad UI. All these years and I still have to pause and think what it really means.

[Fitch]

OTOH, there can be no argument that a check box marked "Don't copy … " or "Do not replace …" is bad UI. All these years and I still have to pause and think what it really means.

Agreed.

I thought I'd also mention, for those that don't go back many years with FileMaker: if that checkbox seems like a bit of an afterthought, that's because it is. Before FileMaker 7 (?), a lookup would only happen when the target field was empty.