And yet another portal filtering question

[KateJ]

OK, this really shouldn't be this hard.... I have been surfing and reading and surfing and reading; FM TechTalk etc. but still don't seem to have a grasp on how to fitler a portal in V7. Any help on the following would be appreciated.

File 1 = Order Header (includes Order ID field)

File 2 = Order Line Items (includes Order ID and qty ordered fields)

File 3 = Order Line Items Shipments (includes Order ID and quantity shipped fields; note -- there may be more than one shipment against one line item)

(I know...this doesn't seem too hard, does it??)

I want a layout to have a portal that shows only the Line Items that still have an outstanding balance to ship. So, I have a field in Order Line Items that compares the ordered quantity against the SUM of quantity shipped in the matching shipment line item records. The result produces an "O" for open if there is still a balance, and a "C" if the order is completely shipped.

In Order Header, I have a text global that simply contains a "C". I have built a relationship between Order Header and Order Line Item (multi-predicate) which matches Order ID on both sides AND also the global in Order Header with the calc with result of "C" or "O" in Order Line item.

Doesn't work. Grrrr. Portal is completely empty. Please help. Thanks, Kate

[onefish]

Kate,

Is the "O" & "C" calculation field indexed? Check the storage options. I recently had the same problem and had to manually index the calculation fields to get the relationship to work. Minimal indexing is all that is required.

[Dan-A]

I think calculated fields MUST be of type "stored calculations" to work in

a relationship.

[SlimJim]

The calculation uses a SUM over a set of related records and I think that will prevent the relationship working properly. Can you script the copying of the calculated result into an indexed text field which you then use to make your relationship? (I have used a similar technique to produce lists of unpaid invoices)

[onefish]

You may also be able to auto enter a calculated result into the field and build the relationship on that field. Untick the box that stops the auto entering from replaceing the the existing contents. I have not tried this to sum over related records but am about to. You may need to use the "Evaluate function" to get it to work properly. It may not be desirable to use use a script to calculate the relationship key. You could have another user updating the qty shipped to be match the qty ordered and the items will remain related through the script created key. It depends on your application I guess.

[comment]

There seems to be a rather persistent myth that somehow auto-enter and/or Evaluate() can be used to make Filemaker something that it cannot do.

There is a reason why a calculation relying on related records is forced to be unstored. FMP is not equipped to monitor related fields for changes. That is not going to change by using Evaluate() as if it were Abracadabra.

Same is true for auto-enter: you might get it to work once - when the record is created. But if it references related fields (or even local calculated fields, that in turn reference related fields), the only way to force it to refresh is to re-enter a value in the key field.

[onefish]

Well I guess thats why you are the old hand. You are absolutely correct. I have had quite a bit of success with auto entering calculated values with the evaluate function in self join relationships. These were required to update when key field contents changed. It does not work when summing accross multiple records. I mucked around with using event script plug-in to set a text field when a value was added to the shipped Qty but it performed unpredictably and triggered the sum script when records were not confined to the related set. So I suppose the easiest way around the problem is to set a text field with a calculated result upon leaving the record. Or, depending on the solution and because people may not use the navigation button with the update script attached to it to leave the layout, it may be better to have the set of fields for orders shipped and a button that commits the information to a portal below and clears the entry fields for the next entry. Of course this button would also update the relationship ID field. I have used this technique before with good results and it can also control many other things at the same time.

[comment]

I believe there may be a simpler(?) solution - see attached.

There are some refreshing issues with the 'Remaining To Ship' portal, but the numbers do update correctly. Anyway, the display in the main Line Items portal could be sufficient on its own.

One thing to bear in mind - there cannot be 2 line items for the same product in the same invoice.

shippedItems.fp7.zip

[Søren Dyhr]

Your solution however, doesn't solve a real life problem, say the customer orders 100 but we're only abel to deliver 70, until we get fresh supplies. I think thats what is ment with:

note -- there may be more than one shipment against one line item

...where you solved it a level higher ...as a business rule to say we only supply full line quantities. This might be sufficient for pre perestroica planned economies. But expectations to people paying for goods up front ...is unlikely. So the more to ship right away ...the earlier the can payments be expected. Stuff in a warehouse is often a liability, and not like interests of a bank deposit.

I gave this thread a thought or two as well - and came to think of autoID'in each item commining into the warehouse. This makes it posible to make a multiline key for each item sold to each itemline. So it's simply to count ID's attached to see if the line is completed.

This requires that two types of info is put into the items table, ItemTypeID and runningID. It's the runningID that is stored in the itemline to make it shipped - the outstanding quantiy plucks in a strained value list via ValueListItems ... I solved this issue as I did in my monkeyclass dwindle

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

Because my hunch tels me that is a tad faster than recursive handling of very large lists after invesigating these:

http://www.filemakerpros.com/POPREM.zip

http://www.filemakerpros.com/POPREM.sit

...I actually made it happen in a template, as a bonus you can handle fluctating prices as well. Since each item arriving beyond getting it's own unique ID could a have cost price to be entered. So via a margin - obviously, could sold items display the fluctation as well...

One thing thoug is that each unique item sold, is put to a coherent shipment document as unique items. But were actually in luck that portals prints so badly that we're used to print summaries out from the portal contens table anyway - because we need to gather in TypeID's.

But since the number of desired typeItems and the assigned items now stands as stored values in each of the portalrow can portal filtering happen effordless - which was the origin of this thread.

--sd

[Søren Dyhr]

So I suppose the easiest way around the problem is to set a text field with a calculated result upon leaving the record.

It remains unstored, with the lack of triggers. You have defitively put your mind on SQL cruise control ...Filemaker is a different beast ...a better relational structure is there to solve it - usually!

--sd

[Søren Dyhr]

Ups I did Comment injustice - He have a update/freshen button at it to solve the issue I raised. Now here comes another question if you look at Larettas thread:

http://www.fmforums.com/threads/showflat...true#Post161249

...then does it carry a lot in common with this thread - however does she use constants which I intutively consider pre FM7 'thoug I can press my fingers on why I think so. But Michael you seems to do it more correctly with the field cUnshippedBalance in you template if we ignore the updating for a while. So a lot of her tri-forked relations are overkill ...aren't they??

--sd

[comment]

Just to make it clear:

There may be more than one shipment against any one line item.

There cannot be twice the same product in the same invoice. That means if you bought 50 nails and the sales clerk filled a line item, then some other items, and just before the total you said, you know what - let me have another 25 of those nails - that's going to break my demo. I suppose it could be made to work without this limitation, but I was only interested in the current problem. It is only a demo, after all.

BTW, note that the refreshing issue inly affects the INCLUSION of an item in the one portal - the numbers do update instantly. I would get rid of this portal and use the regular Line Items with Balance for display.

At first, I too thought constants were unnecessary in 7. But I see no alternative when you need to match a boolean test in the child table.

I think what LaRetta is doing now is not related to this. I suppose she will come to this AFTER she solves her current problem, which is WHEN thing are shipping, as opposed to HOW MANY are shipping.

[Søren Dyhr]

But I see no alternative when you need to match a boolean test in the child table.

But what you do is to make a layover of the foreign key via a calc in the related record, to make it appeare in another portal to the same main record ID ...the same way it's could it be removed again when the next portal needs to get filled!

--sd

[comment]

I suppose you could make it that way too (if I understand correctly what you're saying). I see no harm in using a constant - it might be simpler. Consider -

Parent::constant1 = Child::flag

vs.

Parent::ParentID = Child:<< Case ( flag ; ParentID ) >>

[onefish]

SD,

I'm not sure what you mean that it remains unstored? If the items shipped are entered into the database from a layout and either navigation out of the layout or a button to commit the shipped order also performs the following simple script-

Set Field [Case(Sum(Shipped:Qty)=Ordered:Qty;"C";Sum(Shipped:Qty)<Ordered:Qty;"O";Sum(Shipped:Qty)>Ordered:Qty;"OVERSHIPPED!"; "O")]

the relationship key for viewing the records by "C" , "O" , "OVERSHIPPED!" will be kept up to date and can be indexed. I thought this was probably the simplest way to achieve the desired results with the table structure originally outlined. Of course your solution of creating individual records for each item has many benifits including stock control.

[Søren Dyhr]

I'm not sure what you mean that it remains unstored?

You writes Set Field[ which means a script that have to be trigged somehow! and making

will be kept up to date and can be indexed.

...an overstatement - Take a look at what comment does!

Then however is there a flaw in you reasoning, why bring in "0" twice in you calc'?? In filemaker don't we have "bleed thru" Case( statements, this means that first condition met gives the result which means that anything not meeting the condions uses the default value!

BTW could you make it (faster??) this way:

Choose ( 1+Sign(SumQuantity-Ordered) ; "Open"; "Closed" ; "Overshipped")

Then comes the question, how would you handle this issue:

note -- there may be more than one shipment against one line item

...or even worse as Comment wrote, more lineitems of the same kind in one order document, that if posible goes into the same shipping document ...it's as I see it a many2many relation here - giving false warnings of "overshipped" twice just because the same itemtype occures twice in the same invoice ...if it isn't handled as a many2many. You might wonder when it happens? Think of sets/kits (us/uk) containing several itemtypes that also occures singled out.

But you're actually closer to my principles, than seen at first sight. For me it's much more intutive or metaphoric to execute a script by pressing a button, when trying to ship anything, but in my opinion should it be done whenever a huch of new supply could save the bacon of outstanding orders ...but it ought perhaps to be done in a KANBAN'ish manner

Kanban is a simple replenishment system that is triggered by a real demand-pull system. Since the trigger is based on a realtime need, it is accurate and effective. It is not susceptible to data-entry delay, data-entry error. Kanban works well when integrated with Storage at point of use and other visual systems.

...not even on invoice level, but by running thru all open itemlines to make some FIFO justice - so the script belongs to the commiting of an arrival of new stuf which then generates the shipping documents and change the order status

--sd

[onefish]

SD,

Using some of your advice to an earlier question I had I put together the attached example of how the result can be achieved using the methodology I was talking about. So long as you use only the buttons to perform the required functions, it works perfectly. For this to be effective in a multi-user situation all other options for navigating and new records would have to be removed. You are correct about not needing the second "O" in the above case. It could be "" and the result would be the same. You have obviously got far more experience with FM than I do and I tend to rely on scripting my way out of many problems but hey, the end result still functions.

Orders.zip

[Søren Dyhr]

Indeed very good, then comes the next issue ...do you have a good suggestion to pulling from the warehouse. The issue is that shortage in supplies usually is the problem, and eyeballing what's actually is put in the package isn't always as convenient and not utlizing a datamodel to it's full extent.

Rember a thing some people consider databases - Vessels of Meaning!! Hold that up against eyeballing..

Have you thought of putting reordering to the factory as well as scheduling of taking up outstanding orders when new supply arrives??

--sd

[onefish]

You could build the goods received in the same table as the shipped Qty and use a calculation field with an if statement that turns the shipped qty into a negative number and the reciepted number positive, then sumarize total of the field in a subsumary when sorted by part number, etc. I am curious how you would go about creating an order or recieving goods ergonomically if every item required an individual record. For instance, the warehouse receives 1000 widgets. You would need to script creation of all those records from a single Qty field wouldn't you? Is there an easy way to accomplish this, quickly?

[Søren Dyhr]

I am curious how you would go about creating an order or recieving goods ergonomically if every item required an individual record. For instance, the warehouse receives 1000 widgets. You would need to script creation of all those records from a single Qty field wouldn't you? Is there an easy way to accomplish this, quickly?

There is! ...investigate the attached template.

However isn't it really required to go beyond the calculated repeating field in my idea, since members of a repeating field occures in a valuelist as individuals, so an record of 1000 items arriving contains 1000 serialnumbers in each thier own repetition, which are added to both an ItemLine records field as well as key via ValueListItems( for the shipping document (both as multiline keys) so If 15 indiviual items are sold is it registerd in the itemlinerecord as mulilinekey but in the shipping dokument could it be split this way:

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

...but it's actually overkill to turn a pilcrowdelimited into a repeater and then into portalrows, getting the pilcrowblock into the scriptparameter, and MiddleValues can take GetRepetition's place in my algorithm mentioned in the link.

--sd

massCreation.zip

[Ugo DI LUCA]

Well, I am late on this thread but as the original poster didn't showed back...

There's actually no need to have separate Line Items fro Invoice and Shipments. It gets easy as soon as you get one Table only and a good "tracking" system.