Can item status be anything else - other than "Order Me" or "Ordered"?
And what should order status be when all items are "Ordered"?
Item Status' are:
Order Status' to be:
Waiting On B/O
... and maybe a few more
Well, that's quite a few possibilities you have there - and you didn't say how they match up. For example, some of the items could be ordered, and some received - is that "Partially Ordered" or "Partially Received"?
Anyway, let me get you started with this, and hopefully you can take it further:
Let ( [
items = Count ( Items::OrderID ) ;
ordered = ValueCount ( FilterValues ( List ( Items::Status ) ; "Ordered" ) )
items = ordered ; "Ordered" ;
not ordered ; "OPEN" ;
Fisrt off, Thanks for your quick response!
I See what your saying...
"For example, some of the items could be ordered, and some received - is that "Partially Ordered" or "Partially Received"?"
Yup, it could be both.... but for simplicity, in most cases - all items would have been ordered already as others are set to received - otherwise I want FM to give a screen of death to say "why the hell haven't the other items been ordered yet!!!!"
In your Formula " items = Count ( Items::OrderID )"
What is "items = count" my two tables are "Orders" & "Item Data" is "items" a field, table or a caculation??
I understand the "( Items::OrderID )" mine would actually be "( Item Data::Order ID )" - I think...
Sorry for all the dumb questions... but i'm still too new to all of these srcripts, calculations, relations, etc. I really do try hard hard to figure it out on my own before bugging the pros.
"items" is a variable that I have defined and assigned it to hold the value of Count ( Items::OrderID ) i.e. the total number of order items. See the help on the Let() function for more details.
mine would actually be "( Item Data
Yeah, well... does your database have another table to record something other of Items - something that is NOT data?
I have 2 tables in my database (Orders) & (Item Data) as well as 2 Relationships:
(Item Data::New Entry Relation) & (Item Data::Data Relation)
(Orders) contains customer info and order info (dates, PH, Name, Order ID, etc...)
(Item Data) contains the product info, entered by a portal from the Order layout.
If your recommending that I add another table to make this work, I will... Just tell me what to do to make this work.
Again, I'm new to the calculated data/fields, so the most layman's terms we can use the better... for me.
I don't see that you need another table to make this work - have you tried the calculation I have suggested?
However, apart from the current issue: I don't know your exact situation, but usually you would see a third table of Products, i.e.:
Orders -< LineItems >- Products
so you enter the product information only once (in the Products table) and not repeatedly for each order that includes the product.
I Haven't tried it yet... I'm still trying to wrap my head around your calculation (not the calculation itself) but what my feilds are to yours....
do i create a new field in my (Item Data) table called "Items" or "count"?? or in my (Orders) table?
or do i need to create any new feilds at all???
I'm assuming that my calculated field that i'm trying to create should be in my (Orders) table - and we'll name it "Order Status"
so the Data it needs to calculate will be from the (Item Data) table to create the calculated output...
I have no codeing or programing expiriance... so, again - I'm so very sorry for my FM ignorance, it's only once or twice every two or three years that i actually need revist the FM building/editing process - other than that, I use it every day! I mean, i'd be lost without it.
Thanks for the help!
Sorry.. Just thought about this again.
In Your calculation:
does "items" = my "Item Data" table?
and is your "Items::OrderID" = my "Item Data::Order ID" ?
Here's my DB in short:
"Orders" table fields
Next Portal ID
"Item Data" table fields
Data File Entry No.
(Item Data) New Entry Relation > (Orders) Next Portal ID
(Item Data) Data Relation > (Orders) Order ID
In my formula, you need to replace "Items::" with "Item Data::". Or just rename your "Item Data" table to "Items".
I don't understand your relationships (or perhaps your notation of them?). You should have the following relationship:
Orders::OrderID = Item Data::OrderID
I am not sure why you would need another one or what is the role of the Next Portal ID and Portal ID fields.
I tried your solution with the appropriate edits and it doesn't do anything?
Let ( [ Item Data = Count ( Data Relation::Order ID ) ; Ordered = ValueCount ( FilterValues ( List ( Data Relation::ItemStatus ) ; "Ordered" ) ) ] ; Case ( Item Data = Ordered ; "Ordered" ; not ordered ; "OPEN" ; "Partially Ordered" ) )
Woops... I guess it helps if I set the "calculation result to "is Text" - It Works!
Thanks for the help - now I just need to figure out how to get it to calculate all of the different combinations.
Does this help??
Not really. I am guessing you have a special relationship for creating new items through a portal, but I am not sure how you set it up. In any case, the relationship:
Orders::OrderID = Data Relation::OrderID
is identical to:
Orders::OrderID = Item Data::OrderID
and therefore entirely redundant (I am assuming "Data Relation" is another occurrence of "Item Data").
BTW, you should consider adding fields to Item Data to record the progress, e.g.:
That way you'll have a trail of the events, and not lose everything if someone accidentally picks the wrong status.