Fun with Line Items File

[OffTheDeepEnd.com]

Hello, I have a Line Items file that is used for a portal in my order form. Whenever you type in an item number, it performs a lookup for the price, description and Quantity on hand. What I can't figure out is how I could make a field in order form that would recognize that the quantity on hand of one of the items (which would be viewed from the portal)is less than (2), indicating that there is a possible out of stock. I thought about sorting the relationship by Quantity on Hand but then it throws off all my item numbers, as that is what the current sort is set for. Any ideas?

[Ender]

If your Product file has a Quantity On Hand field, then you can refer to that from the Line Items file with an unstored calc:

OnHand (calculation, number result) = Product::QuantityOnHand

You can have a flag in the Line Items file that compares OnHand to QuantityOrdered and display text or a graphic indicating low quantity or too many ordered:

InventoryFlag (calculation, text result) = case(OnHand - QuantityOrdered < 0,"Not enough in inventory", OnHand - QuantityOrdered < 3, "Low inventory")

[OffTheDeepEnd.com]

We actually have that system setup pretty to the exact specs as you just posted. What I actually need would be a field on the order form that would detect if one of the line items has a possible out of stock. For example if there are four items listed, FM100 (QOH=23), FM101 (QOH=45), FM102 (QOH=-1), FM104 (QOH=16), then how would I have a flag in my order form indicating that FM102 is out of stock, as the quantity on hand field is displayed through the line items portal? Order_form would somehow have to recognize that out of stock and set a specified field to (1).

[OffTheDeepEnd.com]

Bump

[Oldsneekers]

Off the top of my head, (this means this is the first idea I came up with within 30 seconds, so don't hold me to it), I'ld suggest an "Update" button that is clicked after data entry but before any other scripts are executed such as "fill order". The "Update" button would be scripted:

go to field PortalRela::FieldName

go to portal row first

loop

if PortalRela::FieldName <= 2,

Set field (Flag, X)

exit loop

end loop-exit after last

The field Flag is a calc or containor calc field (depending on how you want to show your flag as text or graphic; and X is the signal to the field to display your flag. If the flag is a graphic then X would be the formula get field(GraphicHolderField) The field Flag would be placed outside of the portal.

If you didn't want a separate button, the update script could be the first subscript your "Fill Order" script would run but you would have to add "Halt script" inside the If statement to catch the ordering error.

A simpler answer would be to put the flag on the portal line, but if you wanted to prevent the user from ordering potentially out of stock material, you would still need the loop above to check the portal fields and halt the "fill Order" script (if I am reading your mind correctly).

Good luck.

"It's never so bad that it couldn't get worse."

[Ugo DI LUCA]

Hi,

This can be done with a specific relationship design.

Each line of your LineItems has a Product ID and relates to the Product Database.

Each line has a OrderID and relates to the Order Database.

You can grab the list of ProductIDs related to this order, by using the ValueListItems ( ) and a related Value List using the classic OrderToLineItemByOrderID relationship.

In the Product Db, create 2 calculations :

- c_LowLevel = QOH<2 (returns either 0 or 1) --> QOH must be stored !

- c_CompoundFlag = ProductID& "-" & c_LowLevel.

Then any Product has a key of type Prod0085-1 or Prod0599-0

Create a calculation in the Order Db :

- cVLIMatchLowLevel = Substitute(ValueListItems(Status(CurrentFileName),"RelatedItems"),"

[Ugo DI LUCA]

Correction :

- cVLIMatchLowLevel = Substitute(ValueListItems(Status(CurrentFileName),"RelatedItems")&"

[OffTheDeepEnd.com]

After evaluating the solution, I have come to the conclusion that it would not work because the QOH is not a stored calculation. Unfortunately I cannot make it a stored calculation either because I cannot script our database yet. Do you have any other suggestions?

[Ugo DI LUCA]

Hi,

Dropping some ideas.

QOH cannot be stored in the Product File but certainly can in the Line Items through a ModTime lookup at the moment of the process.

A trigger would index a calc of type Sum(:SjProductsInLineItems) into an indexable number field.

Because this field will stay stored later on, you need to make sure you're targetting the last one though. This involves another trick.

If you think this is acceptable, ask and I'll try to explain it in lenght

What you need then is to identify the

Then you can target your calc in the Line Items.

[OffTheDeepEnd.com]

well, I figured I could also use CobaltSky Auto indexing formula too, basing it off a modtime in the products file. That way there would not have to be a concern as far as finding the latest record in the line items file. Do you think that this solution would be a possibility?

[Ugo DI LUCA]

Hi again,

It wouldn't work in the Product File.

The reason is it works whenever the "current" record is modified, while the calc is based on a related file, so woudn't be considered as a modification to the current record.

Which is why you can however use this technique in the Line Items, and as you originally titled it "play with the Line Items".

It would be tedious to set but not impossible at all.

[OffTheDeepEnd.com]

Yep, I see what you mean. So, now starting with the line items file, what would you recommend then?

[OffTheDeepEnd.com]

OK... here is a thought... I have been toying with a test database that I have two relationships set-up. One relationship sorts by Product_ID and the other sorts by QOH. I defined a calculation field in the Order DB that creates a flag if it finds a quantity lower than two in the second relationship (which has the QOH ascending sort). It appears to work correctly, it just slows down the DB a little. Everytime I have put in an Out of Stock item, the flag activates. Let me know what you think...

[Ugo DI LUCA]

Ok,

So if you use the ModTime trick in your line items, provided you made a change to that record (there are workarounds for this), you can turn any value to an index.

In order to target the very last modified record with same key, you would need to turn a calculation

cLastRecord = Case(RecordID = Max(SjOnProductID::RecordID);RecordID) in an indexed text field t_LastRecordID.

As any modified record could match the ProductID field with this change, you need to introduce a Unique parameter which naturally can be the modified hour, as a start.

So a key can be now cLastRecordKey = DateToText(ModTime)&" | "& t_LastRecordID

In the Product File, produce a list of all related t_LastRecordID text keys using your relationship Product.fp5:ProductID::LineItems:ProductID to get a list of all.

Now, without any script, you can parse out from this list the correct t_LastRecordID and base a new relationship Product.fp5:cParsedID::LineItems.fp5:t_LastRecordID.

This is based on this "latteral" relationship that you could get the Quantity On Hold checked.

If you can get this running in the Product File, then it can work everywhere.

Tell me if or when you got this part running.

[Ugo DI LUCA]

I read your suggestion but cannot see how it could work right now. I may need to see the files for it.

Just to explain where first part would lead you...

Whenever you'd enter a new line in the Line Items, another t_LastRecordID_klu will be filled by the cParsedID by a lookup from a reverse relationship LineItems.fp5:ProductID::Product.fp5:ProductID.

And based on a relationship

LineItems.fp5::t_LastRecordID_klu:LineItems.fp5:t_LastRecordID

another lookup, still in the LineItems, for the current new record will get the last Quantity on Hand for this product in a n_LastQOH

Another calculation

c_Match = Case(n_LastQOH<2;Order#)

Now, based on the Order.fp5:Order#::LineItems.fp5:c_Match, you would get a list of all the Product IDs which QOH is <2

Is that Ok.

It's kind of a complex relationship design, probably those I like more

But it surely works.

[OffTheDeepEnd.com]

For the first solution using a relationship with an ascending sort for the Quantity on Hand field:

In the lineitems file there is a field the is a QOH lookup from the products file. It uses the standard LineItems::Product_ID:Products::Product_ID relationship. Filemaker seems to use the first record in a multiple record relationship to base calculations off of. So, if you set the relationship between the order DB and the Line Items DB to sort ascending by QOH, it would place the item with the lowest QOH first in the relationship, thus being detected by an unstored calculation in the Order DB of LineItemAscQOH::QOH>2. Maybe this solution is to easy to be true, but from all the tested criteria so far, it has held its values. I am just guessing that FileMaker uses the first record in a multiple item relationship, but all tests so far prove that it does.

[Ugo DI LUCA]

For the first solution using a relationship with an ascending sort for the Quantity on Hand field:

In the lineitems file there is a field the is a QOH lookup from the products file. It uses the standard LineItems::Product_ID:Products::Product_ID relationship. Filemaker seems to use the first record in a multiple record relationship to base calculations off of. So, if you set the relationship between the order DB and the Line Items DB to sort ascending by QOH, it would place the item with the lowest QOH first in the relationship, thus being detected by an unstored calculation in the Order DB of LineItemAscQOH::QOH>2. Maybe this solution is to easy to be true, but from all the tested criteria so far, it has held its values. I am just guessing that FileMaker uses the first record in a multiple item relationship, but all tests so far prove that it does.

******* right. How could I overcome such an easy way of doing this.

However, you may want to switch for an other sort key as the Quantity on Hand wouldn't be an accurate parameter.

You may have older records with a Quantity on Hand lower or higher. Remember, these would be lookup, so "stored" at a given time, unless you're triggering a relookup (which I think isn't acceptable in your case)

As it might be possible to modify an order, and thus a quantity on hand, then you'd better catch this modified record as your target record.

Sorry for the complex solution at first. Hopefully you didn't asked for a sampler, which I was planning to create this evening

[OffTheDeepEnd.com]

So, do you think that this would be a decent solution, or end the long run am I going to end up with problems? You also have to remember, all these wonderful tricks I have pretty much learned from you... Most likely you will see a few other nice questions too because we just purchased a VLA for Filemaker 7 including server...

[Ugo DI LUCA]

It is reliable in my opinion.

Actually, thinking a bit more about this, you should only be looking up the Quantity on Hand based on a simple relationship

LineItems.fp5:ProductID::Product.fp5:ProductID

Then, you can either highlight the row if Quantity on hand is insufficient.

If what you want is just a reminder that a Purchase Order should be processed, when being in the Customer Order Module, then you could use another portal to list those articles from this order that needs to, by :

1.In the LineItems :

create a calculation :

c_LowQuantityProducts = Case(QuantityOnHandLookup<2;ProductID)

In the Order.fp5, create :

- a value list of those ProductIDs related to this single order, using the Order#::Order# relationship.

- a calculation

c_VLI = ValueListItems(Status(CurrentFileName);"RelatedProductsList")

- a relationship c_VLI::c_LowQuantityProducts

With 7,I'm happy I could be helpful. As for 7, you'd considerably gain in speed even with these unstored calcs. My learning curve is however at a very lower stage with such a product though.

[OffTheDeepEnd.com]

Yeah, it definately seems to be a great program and luckily I have a course that I will be attending here soon for 7. I am trying to get our database shell switched over then I am just going to import the data. I am sure that it isn't going to be that easy though. Anyway, the reason that I needed all this was just to make filemaker detect if an item is low or Out of Stock, so it would flag the invoice. It definately works with the relationship and now with using SMTPit, I can process 50 orders in 1.5 minutes, which includes e-mail the customer, verifying the items are in stock, and a few other things. It is amazong how far we have come with just a filemaker 4 template. Thanks for all the help so far by the way!