Portal Value list filtering

[hexxabubba]

Thanks in Advance for all the help from everyone on this site.

Included is an inventory solution. Right now you select item number and a value list of item # and item show up. which works great.

What I want to do is select the manufacturer first, and then have only the items for that particular manufacturer show up in value list for item.

Thanks!

[Vaughan]

You want a conditional value list.

[Fenton]

Something like this. I also disabled your lookups for manufacturer, as they didn't really make sense if you filter by manufacturer; ie., choose manufacturer first.

Inventory_fej.fp7.zip

[hexxabubba]

thank you so much for your help! That was really helpful. It has opened up a an entire world of possibilities! I had a calculation in the box beside invoice. It lists the line items by item....

ValueListItems(Get(FileName);"Item_by_Manufacturer" )

Now it lists all the related items. Can you help me set it so it lists each line item with carraige return? Thank you!

[Vaughan]

The List() function DOES separate all values with a carriage return.

[hexxabubba]

i know about the list function, but I can't get it to work like I had it in first file. It needs to be a list of line items from an invoice.

Thanks!

[Fenton]

First, you would put anything like this in the Invoice table. It makes no sense to put it in the Inventory table, I don't think (unless you're wanting to see all sales for a particular item).

The List() function does separate with returns; but if you pass it 3 related fields, it returns only the fields from the 1st related record, not from all the records as it does if you pass it only 1 field. At least that's what I see.

There is a Custom Function which will do what you want, GetRows (and its supporting CF, GetNthRow), by Bruce Robertson, at:

http://www/briandunning.com/filemaker-custom-functions/list.php

It's a little tricky to set up; the parameters are return separated field names, surrounded by quotes. Easy enough, but will break if you change the field names. It also was not happy with "item #", but was with "item_num".

Combined with a custom function Tab (a tab pasted in), and a little tab formatting, it can produce, in a Merge field, an replica of a portal. Which can be used (in desparate situations) for printing across page breaks.

The CF is also useful for "flattening" a portal for exporting, converting to an html table, etc.. But in a normal situation, one has to ask why?

Inventory_fej_CF.fp7.zip

[Erica2]

This exactly what I am working on and there is one problem I am running into. I need to be able to perform a find. In the attached example I would want to enter find mode, select a manufacturer and still get the conditional values.

I am using FM8. This was working in FM7.

I have been trying to figure this out for a very long time so any feedback would be greatly appreciated!

[Fenton]

"I need to be able to perform a find. In the attached example I would want to enter find mode, select a manufacturer and still get the conditional values."

The thing is, the conditional relational filter doesn't work in Find mode in the portal. I suppose because you are really on an Invoice record at that point, on no particular record, so no particular connection to Invoice Lines; you're in Find mode.

Even if it did work, it would not be logical. Because, if it was filtered to show items for the manufacturer already IN Invoice Lines (which is what you'd want to Find), then you wouldn't be able to add new items to an Invoice.

Conversely, if it produced any items by that manufacturer (as it does now in Browse mode), then in Find mode it would show items for which the Find would fail.

Basically a portal is for entering data. It cannot do filtered value lists for Finds.

A relational filter can work on an Invoice Lines TO layout, using a self-relationship on the Manufacturer, showing only items for that manufacturer IN Invoice Lines; but it does not work in the portal, and would not work for data entry if it did, which it doesn't -]

[Erica2]

In the database I am working with my fields are not in a portal. The database I am working with is for dielines. Each dieline can be used for certain products (cheese in this case). I would like to pick from a list of types of cheese (ex. process slices, cream cheese etc.) Then based on this product I would get a list of "modifiers" which would narrow down the search. So I would put in process slices and then in the modifier field I would get a list of slice counts (120, 180 etc.) I have dielines in one table and descriptions in another. I set up a self join with descriptions. This allows me to see a narrowed list when entering data. In FM 6 we had the same database setup to narrow down the modifier in find mode.

Is this not possible in FM 8?

[Fenton]

Anything relationally possible in FileMaker 6 is possible in FileMaker 8. But it's easier -] And you'd likely be using separate tables instead of separate files, since it all seems to be part of the same overall process.

[Erica2]

So if it is possible how is it done? I have tried everything I can think of.

[hexxabubba]

Thanks alot! I really appreciate all your help!that cf is very cool! But the link is a dud. I tried http://www/briandunning.com/ as well. Can you help me get it? Does it cost $?

[Erica2]

Victory.

I figured out a solution to my problem. It was not that the conditional value list did not work, it was the fact that the layout was not based on the correct table.

Thanks for your help!

[hexxabubba]

Found the custom Function!! Thanks!!!

[hexxabubba]

Thanks for everything Fenton. This technique is makining my datbase so much better. For some reason when I copied your cf into another db, I got similar, but not the same results. It has to do with spacing. If you look at screen shot you will see if i take out the "mentary" in documentary, it spaces correctly. Anything longer then it spaces wrong. I don't understand because music & films work fine, and they are 5 letters but "docum" will not work just "docu". Any ideas?

Anyway, the screen shot should be self explanatory.

Thanks!

[Fenton]

I said: "Combined with a custom function Tab (a tab pasted in), and a little tab formatting, it can produce, in a Merge field, an replica of a portal."

What you may be missing is a Tab custom function; which is just a CF with a " " (tab) pasted into it. Type a tab somewhere, then copy/paste it in. You can also just paste a tab into the calculation itself, but a CF is more convenient.

Then, in Layout mode, turn on your Text ruler and add some tab stops to your Merge field (it was inserted by: Text tool, Insert, Merge Field...). Look how it's done in my file to see what I mean about the tab stops; they're visible in Layout mode if you double-click the text box when Text Rulers is on.

FileMaker can do basic word-processing tasks. Many people either don't know or ignore that. (Many people who work in offices with word processers don't use tabs properly either; sad but true). FileMaker is like ClarisWorks or AppleWorks; may they rest in peace -|

[hexxabubba]

you are right again!!!

If I add 3rd field to list; how do I create a tab for that as well? It seems easy enough to add to formula, but do I ned to adjust function so it will tab properly?

Thanks again, you are a genius!