Showing only unique records in Portal

[Flappy]

I have a portal in a Purchase Order Input layout that relates to an Inventory table. The inventory records have various item numbers, and each item number has size variations. When an item has a negative "Available" value, I want it to show up in the portal. The problem is that all of the size variations that are negative availble show, and I want only one of each item. In other words, I need it to function like a value list. I can create a value list and use a drop down list, but I prefer to use the portal if possible.

[Fenton]

Create another table occurrence of the "items" table, related via the item ID, from the Inventory table (if you don't already have one). Then target that instead of the Inventory; ie., "thru" the Inventory TO which has your current filter on negative availibility. The first TO filter will narrow it down to only not-available items-sizes, and the 2nd will narrow it down to only 1 of each item. But you won't know which sizes.

Unless you have the size variations as separate records in the Items table. In that case you're going to need a table with just the item, not all the sizes. Or get into more complicated calculations.

[Søren Dyhr]

You should use this approach sans the numbers - if you shouldn't have a use for them?? I think you do!!!

http://fmforums.com/forum/attachment.php?attid/8407/

For showing each item ID just once in the portal, but to make the negative dissapear in the filtered/summariazed - is a change needed to the field called _cUniqueChildID_ in the portals table, and it should go like this:

Case ( Similar Child::ChildID = ChildID and 1+Sign ( cSumGroup ); ChildID )

--sd

[Flappy]

I do have the size variations as individual orders, and the available calculation references two other "sum" calculations, and can't be stored.

[Flappy]

This looks great. I'll giver it a shot. Thanks !

[Søren Dyhr]

But eventhough the relational approach is straining correctly is the use of the aggregate function not going to scale particular well, since it's not what the aggregate function was designed for.

Ugo's approach which this is works like a charm on small measures of data, but you need to change the approach into a concatanated key (...what I have thought was a bygone) and make it breaker fieldvalue for a summaryfield instead. The way its used in the primarykey for the strained portal is then via GetSummary( function - but there is a problem summary fields needs the sorting of records to work correctly - so by and large isn't there a spreadsheet'ish responsiveness in the establishment of the strained portal, unless you wish to wait forever on the aggregate funtions to work - the matter needs scripting!!!

--sd

[Flappy]

After long hours of trying it every way I could think of, I came to the same conclusion. Whipped a few tricks on it and it works great. Thank you for your help!

[Søren Dyhr]

Whipped, with a script or with an unstored calc??? Lift the lid on these!!!

--sd

[Flappy]

The problem I was having was that I was essetially trying to find the unique values in a found set, and not matter how I constructed the relationships, the first record of the goup occurance was identified as unique. If the first record was not part of the found set all of the child records were still “duplicates”. What I ended up doing was writing a script that imported the records from inventory into a temporary table (at least that’s what I call it) that had a strict validation of the item number as a unique value. This gives me one unique record of each item in the found set (with a negative “Available” value). I then made a relationship from my purchase order table to the temporary file, which gave me the portal with the list of unique items that need to be purchased. Does that make sense ?

[Søren Dyhr]

It makes some sense, but import imply the same data at two different occations, which isn't relational healthy...

You have indeed a point with found sets and aggregate function which I also made reservations about. So genuine summaries is the way to go:

http://www.kevinfrank.com/download/counting-unique-values.zip

--sd

[Flappy]

Brilliant! I knew in my bones it was possible, but I just wasn't smart enought to figure it out. I thank you greatly!

[Flappy]

I ran into a small snag in that I am trying to relate to the summarized records from another table, which didn't work because the results can't be stored. My solution was to create a number field which I replaced the value of the "one if same" field (the unique record key) when I run the script to find the set of records I want to work with. It is only slightly clunky, as opposed to the extremely clunky import script I was using before.

[Søren Dyhr]

I have changed the Ugo/Comment template to abide more to LeCates advice:

Anyhow, I asked about this statement and he says he always steers people towards traditional reporting methods (subsummary reports) for speed reasons and flexibility. Unless there is some really great reason to use relationships to simulate a report, don't do it. FileMaker was not designed to aggregate massive amounts of information through relationships. Anyhow, I just wanted to make sure readers were clear on the best approach to reporting.

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

The algorithm used is BTW this:

http://www.onegasoft.com/tools/fastsummaries/index.shtml

--sd

SummaPortMOD.zip

[Flappy]

I will have to ponder this new example a bit. It agian looks very interesting. I have attached a screen shot of my Purchase Order Input layout. PLease excuse the stray fields and buttons, I am in the process of reining my interface. The bottom portal is where I am using this relationship. The items displayed in the portal represent the group of items which have a negative value in "Available". The button in the portal row is used to choose the item to buy. The portal above it displays the various orders the item is needed for. The user can click the "job" button to poulate the order quantity fields with that jobs quantities regardless of the available quantities (we quite often buy shirts by the job, even if we have some of that item in stock). The user also has the choice to click the "all" button to buy all needed quantities of the item. The solution you suggested yesterday worked very well, and only required a short simple script and an additional field.

[Søren Dyhr]

So if I get it right is the process to choose vendor, and by the selection is the next tab chosen. In the lowest third is a shirt-type and a colour selected by the button to the right. What happen is that two global fields is filled with... - here S533 and eggplant, which pulls up two horizontal portasl of the same relation, one showing only the sizes that holds a negative stock the lower and the upper a lot of empty fields in the same table to fill in the desired quantitees, using the summary method I just have shown.

When an order is processed are these numbers turned into new adjustmens of the storage, by making the needed number of records, while the numbers entered are cleared, so they don't show up the next time you hit a certain shirts button.

But there exists no impediment that prevent us for the fully scripted version, since it's fast enough ... in my humble opinion, whith the plentora of buttons availiable!

There exists other methods, such as lookup last - but it have a snag to it, that canceled orders needs an extra ledger line to counter the original.

--sd

[Flappy]

This is great, Soren. It accomplishes the same thing I was doing, but with in a much more sensible way. Thank you for your help!