Calculation/portal question

[fmp6andfmp7]

My fmp7 file has three fields item, location, quantity. For each item there can be multiple quantities at each location and there are 15 locations. I want to know the two locations that have the smallest amount of quantity of an item. The rest of the locations can be deleted or omited.

I can see that using a portal or some type of script would work but I just dont have the FMP skill to do it. Any help would be great!!

[Osman]

As you say you can see two smallest values by portal.

To do this create a portal and sellect sort option (sort by quantity) than set number of rows 2.

Only 2 smallest values will be displayed in portal.

Adam Djuby

[Kent Searight]

For each item there can be multiple quantities at each location and there are 15 locations.

It seems to me that you need more than 1 table in your file before you can even begin to accomplish what you want. Take a look at this example...it might give you ideas on how to proceed. Good luck!!

ItemsLocationsQtysExmple.zip

[fmp6andfmp7]

Thanks, Adam Djuby and Kent_S! Ok I'm close but I cant seem to wrap my mind arround it.

here is how the data is laid out in the csv file I have of all the items, quantities, and locations:

item quanty location

smurf1 50 ny

smurf1 20 mn

smurf1 24 ca

smurf1 12 il

smurf1 7 fl

smurf1 58 tx

smurf2 90 ny

smurf2 22 mn

smurf2 64 ca

smurf2 19 il

smurf2 51 fl

smurf2 2 tx

For each item there are at least 4 locations. I have to import from this csv everyday. The info I need is which two locations has the lowest stock.

So in the above example it would be:

smurf2 tx

smurf2 mn

smurf1 fl

smurf1 il

I know this can be done via a portal or something but I need more help, this is my first portal. I read all about how to set them up but from concept to FMP doing it is beyond me. Any help would be great!

[Kent Searight]

In order to steer you in the right direction, a couple of questions to determine your knowledge of FMP are in order:

1.) Do you understand why a relationship to a second table is needed and do you know how to create relationships?

2.) Do you know how to put a portal on a layout and then set it up?

[fmp6andfmp7]

1.) Do you understand why a relationship to a second table is needed and do you know how to create relationships?

2.) Do you know how to put a portal on a layout and then set it up?

1. I do understand and I know how to create relationships.

2. I do know how to set up a portal on a layout.

I have been working from you example file and from your help I have set up the portal and it works fine, in taking the lowest two locatioions, thank you!

But the problem I have is to be able to use that data.I need to define a field to hold each of the low qty locations, Low_loc_1 and Low_loc_2 or in some other way be able to export as a csv the values of the portal(low1, low2, along with the item name as one record.

[Kent Searight]

Write a script that imports your CSV into an Inventory table. See new example...

ItemsLocationsInventoryExmple.zip

[Kent Searight]

Oops, I forgot to include the script for finding the values you want. Add to it as needed.

Good luck :

ItemsLocationsInventoryExmple.zip

[comment]

This can be done "live", without a script.

2ndLowest.fp7.zip

[fmp6andfmp7]

Thanks for all the help. The problem is that I cannot access the lowest two for export. I need to get the lowest two locations, qty, and item either to export or as one record with the fields: item, lowest location, qty, 2nd lowest, qty. Is this something that can be done? Thanks

[Kent Searight]

: Very nice, comment....but how on earth are you able to use the 1 field from another table to return 2 different values in the same record? And why are those values the 2 lowest?

This is sooo cool, I'm darn near giddy waiting to hear how you did it!!! : I've tried to figure it out and I'm completely stumped.

[fmp6andfmp7]

: Very nice, comment....but how on earth are you able to use the 1 field from another table to return 2 different values in the same record? And why are those values the 2 lowest?

This is sooo cool, I'm darn near giddy waiting to hear how you did it!!! : I've tried to figure it out and I'm completely stumped.

Im the end user and this is far above my pay grade. Any ideas about a way to get these lowest records out of the file and into a csv?

Edited September 14, 2005 by Guest

[comment]

I cannot access the lowest two for export

Ah. Try this then. I have set up an embryo script for export, so you can see which fields to use.

Kent: read Adam's suggestion above. Then look for 2 invisible portals, and see how they're set up.

2ndLowest2.fp7.zip

[Kent Searight]

Got it! Thanks, comment!

[-Queue-]

Hmm. It seems like changing cSortHelper's definition to

Right ( "00000000000" & 100 * Qty; 11 ) & " " & SerialID

should resolve this.

[jason P]

HI,

This forum is cool, never knew thing existed. Anway this is sortof the problem I have too. I was messing arround with the example download but how do you import new stuff into it. There are three tables but I havent done a import like this before. Can you set up a script to do an import into the file so that I can get the example.

[fmp6andfmp7]

Just import three times, once into each table. Only import into fields that can be imported into.

[fmp6andfmp7]

Ok, it looks like I have a problem. I need the lowest three fields. Since I still dont understand how the portals all work can someone help? Thanks

[comment]

It's only a matter of duplicating a field and a relationship. I have also changed the sorting to accomodate fractions of 0.25.

Note that there are no portals in this file. If you want to understand how it works, study first how the value list is built (and why). The three cLowKey calculation fields simply extract the first, second and the third value, respectively, of the value list. The relationships are then built on the extracted serialIDs. But it is the value list that does the work here.

Another point worth noting: if you have equal quantities competing for the 3 lowest positions, you will only see the first three, alphabetically. For instance if item quantity is 10 in ALL location, you will only see CA, FL, IL (in the demo), ignoring other equally under-stocked locations.

3Lowest.fp7.zip

[fmp6andfmp7]

Ok I have the file working and I understand how it works. Took me awhile learning about portals but I got it. Now my problem is with getting rid of the duplicate records I dont need. I have the lowest two items but I also have the rest of the items. I have made a duplicate record checking script that gets rid of the duplicates for each record but this also messes up the relationships deleting info from the locations and putting in false info into the locations.The overall goal for the attached file is to have the lowest three locations for each product available for import into another FMP7 file.

lowest.fp7.zip