HELP!! calculation problem

[Robert Collins]

I am desperate I have a database that needs to be in action by 1st Aug and I'm stuck.

I have an invoice database that includes a stock table and and invoice table. Everything I book in to the stock table has a serial number and a creation date (as well as a product code and description that is looked up from a product table) . For various reasons I need to sell the stock in the order that it was booked in -not via a serial number as these are already on the product, but by the date they were booked in. I need a custom message to be shown when someone tries to sell a product that is newer than an older one that is still in stock. I hope i've explained myself clearly - any help would be appreciated.

thanks

[CobaltSky]

Hello Robert,

You are talking here about process management - ensuring that at the point of stock selection the correct item (according to the business rules) is added to the invoice. Process management can be approached positively or negatively. If approached positively, the user is guided towards the right outcome, whereas if approached negatively the user is admonished for an incorrect outcome. In general the positive approach provides a better experience for the user.

Relating that to the situation you;ve described, rather than worrying about custom messages(which won't appear until after the user has made a mistake), you may be better off simply having your database set up so that when the user selects a product, the one which is placed onto the invoice is always automatically the earliest booked item from that product line.

To do that, I suggest that you establish two relationships to the stock table (from the invoices table). One relationship will show the available stock so that the user can select an item to add to the current invoice. The second relationship should match the selected item productID to the stock table and should be sorted in ascending order of the booking date.

You should then provide a script which adds a selected item to the current invoice, which first places the productID of the selected item into the key field for the second relationship, then places the stockID sourced via the second relationship into the invoice (or into a related invoice lines table). The first stockID sourced via the second relationship (the one sorted on book date) will always be the earliest booked item from the selected product line.

If the script you provide is the only way that the user can add a product item to an invoice, then there should be no need for an error message, since you will have set up a process which always operates within the rules you have defined. :)

[Robert Collins]

thanks for the reply.

The reason I wouldn't want the database to select the serial number is that I might have 20-30 of the particular item in stock and when the shop gets busy , time would be wasted trying to fish out the right box on the shelf.

our stock shelves normally have the oldest products at the front - the calculation (via a value list maybe) is simply to stop newer products being sold. The fustrating thing is that I had a database in filemaker 5 for the last 2 years that did exactly what I am trying to do now.but all the information is now lost and I simply can't remember how i did it!

This is what I have done so far

the serial number field is set to unique value - this is so there can be no duplicates. -This works great

Validated by a calculation

"exact (Product description ; product looked up from serial number" - this makes sure that the serial number is not only one that has been booked in via the stock table , but belongs to the product type) - this works great

I have also created a lookup field in "invoices' that looks up the creation date of the particular serial number entered.

All I need now is to create a field that looks up the lowest (oldest date) that is still in stock of that product.

I tried creating a auto enter calcation using the Min function but I had no luck.

[TerenceM]

Sounds like you want a relationship from Invoice to Stock based on ProductCode and sorted by date (ascending). Then check to see if the creation date of the current item being invoiced is <= the creation date of the first Stock item via this relationship. You could build this into the validation calculation for the serial number field, although it makes for an unfriendly error message (since the validation can fail for either of two different criteria, and you can only have one message).

Also, I don't remember the execution order of validation calculations vs. lookups. You may need to pull up the creation date of the current item being invoiced directly in the validation instead of relying on the separate lookup field because the lookup may not occur until after the validation calculation occurs.

Hope this helps.

-terence

[Robert Collins]

thanks, i shall give that a try.

While messing about I seem to have created a field (in my sales table) that puts the oldest date a product of that type has been booked in (using a relationship of product description from stock). and I have already had a field that looks up the booking in date of the serial number entered. Sureley all I need to to now is create a calculated result that the booking in date=oldest serial number date. The only problem I can now see is how would it know only to put the lowest date of a product still in stock and not just lthe lowest ever booked in.

I was thinking if I had a lookup field in stock table that shows the invoice number beside it when it is sold - then some sort of IsEmpty calc would filter out anything that is already sold.

The problem I had with a invoice number lookup is when I changed the serial number in Invoices, the serial number of the item in stock still had the invoice number next to it - it didn't clear. Is there a way round this?

[TerenceM]

Sounds like you should be using a calculation field in the stock table to check if the item has ever been sold, as opposed to a lookup. Something like:

if (IsEmpty(Invoice::InvoiceNumber);"in stock";InvoiceNumber)

Note that sorting this field may be screwy because it would need to be a text result. If your InvoiceNumbers aren't all the same number of digits, then you may want to use 0 instead of "in stock" to indicate its state, and not display the 0 value using the number formatting options.

Then you can add a criteria to the relationship that checks for the oldest item still in stock, using a global with the value "in stock" to only pull up those stock items that haven't been sold.

-Terence

[Robert Collins]

Excellent idea - I shall use if empty.

the last bit went a bit over my head (due to my lack of knoledge!) could you give me an example.

thanks again

[Robert Collins]

I have just tried the IfEmpty calculation but but it doesn't change from 'in stock' to the invoice number unless the serial number is already on an invoice and then I book that serial number in - in other words I need it the other way round :crazy:

[-Queue-]

The problem I had with a invoice number lookup is when I changed the serial number in Invoices, the serial number of the item in stock still had the invoice number next to it - it didn't clear. Is there a way round this?

Why would you change the serial number of an invoice? It should be auto-entered and never changed or you break any relationships that use it as a key field.

I do not understand why you would want 'in stock' to show when an item has been invoiced. However, just flip the true and false results in your If statement to produce the opposite results,

If( IsEmpty(Invoice::InvoiceNumber); InvoiceNumber; "in stock" )

unless your problem is that you are using an auto-enter calculation instead of a true calculation field.

[Robert Collins]

Sorry if I didn't myself clear

1) I don't want it to show "in stock" when a product is invoiced - I want it to change from 'in stock' to the invoice number.

2) When I say 'serial number" ., I don't mean the sequential invoice number , I mean the products individual serial number.

In my business , I need sometimes to change the products serial number (this comes pre-printed on the product) its rare but it does happen.

Using the IfEmpty funtion - I expected the record to show in stock straight away (which it did) and change to the invoice number when it was sold - this was not the case - the only way I could get the invoice number to show was to sell it first then book it in - which is not right

[Robert Collins]

I have attached a small example of the database i am working on. this only contains enough information to hopefully solve my problem.

I made notes where needed to explain what is happening in each field.

Anyone up for the challenge

thanks again

[Robert Collins]

Right, I have sorted the IsEmpty situation (thanks Queue, I needed to set it to calc field not auto calc)

Now all I have to to it sort the field that looksup the oldest serial number of that product in stock - (at the moment it just shows the oldest date regardless of whether its sold or not)

at the moment it reads

Min (Stock: date in)

I know what I want to say but I don't know the calculation.

I want:

Min (Stock: date in) when Invoice_number = ""

[-Queue-]

Well, you need to Zip your attachment before posting it. Just make sure it is closed first.

[Robert Collins]

Sorry about that, try this

[-Queue-]

Try defining the the serial number lookup relationship to sort by Invoice number ascending, then Date ascending. Then make your oldest date field a calculation (not a lookup or auto-enter calc, unless you want to have to manually update it) of

If( IsEmpty(serial number lookup::Invoice number); serial number lookup::date )

With the relationship sorted this way, the first related record will be one with the earliest date and null invoice number (if there are any). If there are no such records, the calculation will not return a result.

I am a little confused though. Since you have the Product Serial Number validated to be unique, it would seem that there can only be one Invoice per Stock serial number. So sorting the relationship would be overkill, if there is only one record to sort.

[Robert Collins]

Thanks , I tried the sort by invoice number and date and this works great.

I have also changed the relationship to the product description fields i already had.

the IfEmpty calc didn't work though as I always need to return a result. So I have had a fiddle and just used a Oldest date = Booking in date (via the sort) and all is well.

thanks