Count based decision script

[MnR]

hi,

another complez script, i now know how you guys think so i know what info to supply!

Firstly I need to display the number of turkeys left to order. I want to create a field which displays how many left their are in order to inform the user.

This could be done via a script or calculation.

ok where we go,

First count orders where WeightNeeded is not null (or in other words filled in) from tbl_orders

then compare to value GlobalTurkeysLeft.

put result in box.

[MnR]

ah but because users can cancel orders the script must also check to see if a field called "Cancel?" is not set to yes, because if it is set to Yes that means that the turkey counted for that order must not be counted...

[Søren Dyhr]

Say these birds each have a serialnumber attached in the system beyond the weight - then could the reasonings behind these be combined

http://www.fmforums.com/threads/download.php?Number=133792

http://www.fmforums.com/threads/download.php?Number=144255

...all it then takes is to aggregate over the portals content ...Count comes to mind!

So it's two tiny calc's - when it comes to it!

--sd

[MnR]

hmm i don't really see how this answers my question and also the "birds" don't have ID's as the data is stored in repeating fields (5) so an ID would just get repeated and i had already thought of trying that.

[Søren Dyhr]

Then do it - repeaters are for utilities...while being great bysantine fun - aren't they a good substitute for a many2many relationship. Others have led you to believe the learning of relations, could be prosponed for indefinate time - so you better address them instead!

--sd

[MnR]

No, i am sorry but this is a school project and not really for use i just need a way to count the fields which have WeightNeeded occupied and Cancel which is not set to Yes. I would change the structure of the database but this would require too much work and the project has to be in on monday!

[stanley]

MnR:

Repeating fields? Bad practice. You may not like it, but you're dealing with professionals who are dealing out professional advice.

Regardless of that, just in general terms (it is too late at night for me to be precise) what you need is to do a FIND for the reverse of your criteria (because you didn't say cancel had to be set to "no", but rather not set to "yes", and that WeightNeeded had to not be null), which would be: cancel="yes" & IsEmpty (WeightNeeded). Then find omitted, and there's your set. (The other option is to do the same thing with a Case() statement and a loop to count the results.) Now count them and you're done. Yes? Or did I not understand the criteria correctly?

-Stanley

[Søren Dyhr]

Allright I've boiled the two concepts I talked about into a many2many sans join-table, now could you make countings on the ID's in both portals If you wish ...the issue to prevent here was apparently the doublebooking of a bird for the same Thanksgiving or X-mas ...Attention should be made to the way the keys in the turkey dept are made. If these measures weren't taken would the removal of 1 also cancel 11 and make 10 into 0..

Anyway enjoy ...In case you're using previous versions of filemaker could the concept be done this way:

http://www.dwdataconcepts.com/dl/tm/Many2Many.sit

http://www.dwdataconcepts.com/dl/tw/Many2Many.zip

But it violates in the pre 7 version, the rule not to tamper with the clipboard, so a clipsave measure should be taken. Fortunately does the new relation types solve this elegantly!!!

--sd

Stab_a_Turkey.fp7.zip

[MnR]

hmm interesting design, if i show you what i have done so far this might help visualise what i really need the database to do!

[MnR]

But the main problem with the stab_a_turkey is that customers have to place orders and then after the turkeys are grown killed and weighed are they entered into the database, the solution would be good if their wasn't a gap between order put in and turkeys in stock.

The key points of the database are:

1.Customers can place an order, a order must not allow more than 5 turkeys to be ordered.

2.Turkeys are assigned to "requested" weights by the customers in their orders.

3.orders can be deleted but cancelled.

4.Bills have to be produced for customers.

5.Weights requested are between 5 and 10 kg and are to 1 decimal place eg. 5.5kg.

other rules i was going to apply:

1.No orders before november

2.No order after 22nd of december as orders are collected on 24 so 2 days to kill and prepare.

those are a few of the basic rules in the post before i have attached my current solution which isn't the greatest but should help to try and show you what i am trying to do!

[MnR]

why didn't it attach!!??

[Søren Dyhr]

The way the valuelist is build could be more dynamic, making it dependant on both the scheduled time for the delivery as well as the desired weight class.

orders can be deleted but cancelled.

You might have noticed that some overkill exists inside the template, because I actually toyed with the idea of having cancled stuff, but it weren't obvious to me what then would happen to the abandomed turkeys ...are they supposed to die of age instead???

--sd

[MnR]

sorry typo they can only be cancelled not deleted, i solved many of my problems by following your advice and stoping the user of repeating fields and have now got a 1 to many relationship with a portal.

Ps. i found quite a few problems with the stab a turkey, like if all turkeys are set to free, then everything disapears?! good design and idea though...

[Søren Dyhr]

Regarding the PS. You're right I'm goin to solve it during monday.. unfortunatly is it past your

I would change the structure of the database but this would require too much work and the project has to be in on monday!

--sd

[MnR]

No i think you meant to say is it past my deadline? In that case no. And I HAVE changed the structure to 1 to many and am using a portal!

[Søren Dyhr]

I knew it was something simple - relationkeys doesn't resolve when empty - this is why the minor change in line 3 of the script was in need for an extra pilcrow before ValueListItems(

Set Field [ Orders::TurkeysAssigned; Substitute ( Orders::TurkeysAssigned ;Turkeys to this customer::TurkeyID ; "" ) ] 

Go to Field [ ] [ Select/perform ]

Set Field [ Orders::KeyToAllSoldTurkeys; "