Auto enter Available Quantity

[sherifmayika]

Hi all,

Hoping someone would help me I am submiting an enter enter calculation that killed my sleep for last few nights.

I have an invoice db. In invetries I have "quatity available" as auto-enter

here is the calcuation

Case ( Quatity Available ≤ Reorder Quatity ;

TextStyleAdd ( TextColor (Quatity Available ; RGB ( 255 ; 0 ; 0 ) ) ; Bold ) ;

TextStyleAdd ( TextColor ( Quatity Available ; RGB ( 119 ; 119 ; 119 ) ) ; Bold ) )

this calcuation gives me the expected result

The problem i am facing I want to add the "purchase quaity" to ""quatity available" and minus "Quality sold"

any kind of help would be apreciated

Edited September 11, 2006 by Guest

[CobaltSky]

The problem i am facing I want to add the "purchase quaity" to ""quatity available" and minus "Quality sold"

Hello sherifmayika,

I assume that by "quaity", "quatity" and "Quality", you actually mean quantity?

That being the case, providing your database has a procedure in place for storing or calculating the quantity that has been purchased and the quantity that has been sold, the answer is simple arithmetic:

quantity available - quantity sold + quantity purchased

I have a feeling that there is something more complex about the problem that you are trying to solve, but I'm afraid it is not clear from the way you've framed the question.

[wernerf]

I'm somewhat of a new user and have a problem that I can use some help with. I have a number field that has a value (marketing cost) that is attached to a certain person for a certain month.I would like to equally divide this value by the number of jobs (orders) that this person has closed in the same month.

What is the best way to accomplish this task?

Thanks!

[Søren Dyhr]

Here GMT +1 is it too late to make you a template, but I might whip something up for you tomorrow, the challenge is great as I see it ...because it likely to be transaction'ish - and where should the trigging of the unavoidable (I think?) script be done most conveniently.

--sd

[sherifmayika]

Thanks for the response.

Excuse me for the English mistakes which is my 3rd language.

I know this calculation would work. But my problem is slightly different.

“Quantity available, quantity sold, quantity purchased” are not on same table

Actually I have 4 tables ‘Inventory’, ‘item’, ‘Invoice’ and ‘Purchase’. ‘Reorder quantity’ and ‘Quantity Available’ are belongs to ‘inventory’

‘Purchase quantity’ and ‘Quantity sold’ are from item table

Item is a portal both on ‘Purchase’ and ‘Invoice’ layouts. ‘Item’ and ‘inventory’ are related

As I sale an inventory item using invoice layout I want automatically minus the available quantity. Otherwise is in the case purchase.

Besides I want to change the color of the text if available quantity is < or = reorder quantity.

[Søren Dyhr]

Alright I promised you a template which is an etude for me, seeking to get suggestions to simplifications from other users of this froum.

This method uses the creation of new lines in the invoice to run a "fastsummary" that gathers all the itemID's availiable of the desired type, it uses that a lookup picks the first occurence of a match even there is more than one, the technique is then a mixture of Denny Schlessingers old template "The Lease" and Michail Edoshins "Fastsummaries" for being able to scale appropriately.

The problem you face with "The Lease" is that it uses a scripted replace to go every item in the warehouse portfolio, and tags binary if a relational match exists or not. Scripted replaces are pretty fast, but every measure to narrow down the found set is wellcome. Here comes in both the new fm8'ish GTRR(FS) and the Fastsummary in handy.

To establish the availiable of each type, do I again via the Fastsummary algorithm get the number of records in each set, which can control this CF:

http://www.briandunning.com/cf/439

What yet is missing in my template here is to guard the users from each others found set of first occurences of each type, to make the lookup work on a multicriteria using the username in both ends only works partly ...further more must the access to the tagging process lock other users out temporarity!

It should be said that other methods exists, such as the one expoliting recursive relations to calculate inventory levels as descriped in Chris Moyer and Bob Bowers book:

http://www.amazon.com/Advanced-FileMaker-Techniques-Developers-CD-ROM/dp/1556228597

...starting on page 307, but the plentifull use of aggregate functions, is however not fully embraced by LeCates:

http://www.fmforums.com/forum/showpost.php?post/206543/

Which is why I chose the method in the attached template! I'm fully with that it isn't rocket science and the script could be tightend further, by using multi criterias more than I have done ... the gathering of records could be narrowed down by the use of < zzzzzzzzzzzzzz relationtypes, still leaving the empties out of the equation - but I have chosen to let the explorer of the method to carefully examine each of the applied requests. When tracing it with the debugger should provide the needed clue.

Before I end - do I need to [color:red]caution, this method only works with fm8+ !!!!!

warehouse.zip