Creating a default found set from inputted field values

I have a produce table that records certain values for each of my supplier's produce. Most of this produce supplied is used to populate a vegetable box that is delivered to our customers. (But not all of them... )

Our vegetable box sizes are small, standard and large: in our produce table I have three fields. One is called small, one is called standard, and one is called large. In each I enter the weight amount/quantity amount of the produce I would put in the corresponding boxes: ie 1lb in a small box, 1.5lb in standard box, 2lb in a large box.

Then when a customer places an order, I enter generic details, including the size of Vegetable box they've ordered. I also have further details for their order which is a found set made up of all the produce we are supplied. Here I can enter the quantity amount of each produce ordered: this gives me the opportunity to customise vegetable boxes should our customers dislike a particular vegetable, or they want more of one, compared to another.

The point is, can I get the quantity amount in the more specific details to originally default to a small box size, standard box size, or large, depending on what the customer has ordered, with the ability to change  the quanity if needed?

If I haven't explained well enough, please tell me, and I will try to elaborate!
LVL 1
butterhookAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lesouefCommented:
yes, of course...
create a field box_size in customer_order_lines.
create a list boxes = small, standard, large
assign this list to the box_size field, as popup menu.

make the qty field auto-enter/calculated/do not replace.... (this last one if you want to avoid further change of the qty if you hit the box_size popup after qty has been entered.)
field calc:
Case (
box_size = "small" ; produce_in::qty_small ;
box_size = "standard" ; produce_in::qty_std ;
box_size = "large" ; produce_in::qty_large ;
0
)

BTW, who makes the boxes? you or the supplier? if the supplier, you'd better not change the qty but change the amount of boxes?
As usual, I have an example, based on your previous file, I can send it to you if you fail in doing this. but that's pretty easy.
0
butterhookAuthor Commented:
Lesouef, sorry for the delay - have been up to my eyeballs as it were - and have only had a chance this morning to get on with this problem...so I will try as you suggested, and get back to you if there's any problems... thanks for the swift reply...
0
butterhookAuthor Commented:
Lesouef, shouldn't I be creating the box_size field in customer orders - and not customer_order_lines, as otherwise, the box_size field is going to be present for each produce listed...
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

butterhookAuthor Commented:
I seem to be getting closer, but quantity still isn't populating with the correct quantity amounts...
0
butterhookAuthor Commented:
And another thing: how can I get it to repopulate the quantity amount if a customer calls to ask to upgrade her small box to large? Oh, so many questions!
0
butterhookAuthor Commented:
Can you post your example Lesouef so I can see where I went wrong. I just can't seem to get the quanity amount to get calculated...
0
lesouefCommented:
if the box-size is in the customers_orders_lines, it is because I thought you could order a small box of carrots and a large box of beans in the same order; so what? customers must use identical box sizes for the items of a given order? not very logic to me...
repopulation: so far, you can alter the qty after the box size has been set. if you change the box size again, I have prenvented qty to change again, in case you would hit the box size again by accident, and thus overwrite the custom qty you may have entered in between. this can change for a dynamic behaviour, ie any box-size change overwrites the qty. I could even enable in this case qty to be overwritten only if the qty matches any of the available box-sizes and disable it for an existing custom qty. that is not in the current example. just tell me, I can't decide for you, this is really a user's decision depending how you handle customer orders.
file: https://filedb.experts-exchange.com/incoming/ee-stuff/5799-butterhook5.zip
0
lesouefCommented:
Just modified the exmaple for the last thing mentionned which seems the most logic to me. not sent to you yet.
One thing: if you alter the qty in a box, how can you stuff the veegees in there ? or do you always lower the default value? and why not choose the box from the qty by choosing the box size which gives the least amount of boxes? (may not suit the customer...)
0
butterhookAuthor Commented:
Just so you understand, the way it works is that our customer either orders a small, standard, or large vegebox size...we place a selection of seasonal vegetables within it without our customer having control of that. They can however state two dislikes, and we then make up the difference. Furthermore, they can order extras, which we then add to their order... so if they order a small box, I want the quantity amount of each vegetable we place in a small vegebox to be automatically populated. This can then be edited if they dislike it... do you see what I mean?
0
butterhookAuthor Commented:
The point is, we don't supply a box full of just carrots, or potatoes, or leeks etc. We supply a box full of seasonal vegetables, with all sorts of vegetables in it. But if it's a small box, we only put small_quantity of lbs in the box, if its standard, we only put standard_quantity of lbs, and large, large_quantity amount...

I'm sorry if I haven't explained it well enough, but I'm sure you've got a better understanding of what's best than I do! You normally do!
0
butterhookAuthor Commented:
Lesouef - what you have done is great! I think I just didn't explain full enough... the point is that for each customer_orders_lines, there will only be one vegebox size. If in customer_orders_new, vegebox_size is small, then for customer_orders_lines every produce will default to qty_small. Is that more clear?
0
lesouefCommented:
ah, you are mixing the whole stuff in a single box!!!!!!!! why don't you make the soup strait away!
so...
please wait, I have to review the whole process...
One box fits all !!!!!!!
Still afraid the beans bet smashed by potatoes...
0
butterhookAuthor Commented:
Ah - you are astute lesouef - we have compartments for such occasions - fancy a job packing boxes! He he he!
0
lesouefCommented:
> fancy a job packing boxes?
I am still probably better on databases, but I might end up doing that very soon...

now modified according the latter explanation, ie the box with the "avoid the mess compartments"!
https://filedb.experts-exchange.com/incoming/ee-stuff/5802-butterhook6.zip
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
butterhookAuthor Commented:
Quite brilliant!
0
butterhookAuthor Commented:
Absolutely marvellous once again Lesouef...
My quest now is to ask a question that you can't answer! He he he. I think I'm going to find that difficult!
0
lesouefCommented:
I know the answer to that too!
0
butterhookAuthor Commented:
he he he - expect to hear from me soon!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.