Pop-up Menu with specific looked-up items

[MarcS]

Hi there,

I am trying to create a menu based on the following:

I have a catalog with a description field and a code field. There are lots of items, but they can be categorized, so for example, I have ( - description - code)

- Red Door - 111

- Big Door - 222

- Glass Door - 333

- Wood Table - 444

- Steel Table - 555

etc...

In another FM file, I also have a browse_description field looked up via a browse_code field. What I did so far was a menu with all the codes from the catalog (using a Value list, code field from catalog file). What I'd like to do is be able to enter a small text in browse_description so that the browse_codes would get trimmed to the relevent items only. So if I write "table" in my browse_description field, when I click on my browse_code menu, only the "444" and "555" values show up.

I was thinking of adding a "Category" field in the catalog, but it still has to be an exact match. Idealy, it would search the entire catalog description field...

Thanks !

[danjacoby]

Split the description field into two fields, or (if this works) create a calc field that returns RightWords(browse_description, 1). Now create a relationship between browse_description and the second of the two fields (or the calc field).

Now you can have the value list equal the related codes.

[CobaltSky]

Hello Marc,

Here's a solution that will provide exactly the functionality you are looking for:

1 Create a global text field called gItemSearch and place it on the layout next to your pop-up list, for users to enter a word describing what they are looking for into (eg 'table').

2. Create an unstored calculation field (result type text) called cItemSearch with the formula specified as: Case(IsEmpty(gItemSearch, "AllItems", gItemSearch).

3. In your related file, create a stored calculation field (result type text) called cItemDescription with the formula specified as: browse_description & "

[plechton]

Hey there!

This is perfect solution for problem I am having!!

It is like custom filtered value list, which I never thought was possible, though I knew about filtered portals, but I never thought of this!

This will be brilliant solution.

Thankyou!!!

Pauline

[MarcS]

Hi CobaltSky,

Your solution is indeed what I was looking for ! It's was I was going to do with the Category field, only yours fills it automatically, with all possible solutions ( "Red Door" ends up in both the "Red" and "Door" categories). Nice !!

However, after trying out my simple category thing, I realized I couldn't use this.

It's actually for a submission page, on wich I have 30 lines. Each line has the search field, the code field (menu), dimensions, price...

Since I never thought I'd use something like this, the whole thing is built using repeating values. Works great for everything, except this : only the first search field is considered. All repeating menus are based on it. Using Line Items & Portals would be a work-around (I think), but I'll first see if it's really worth all the re-code... a lot of other pages depend on the submission page.

Thanks for the great code though !! It'll probably come in handy..

Marc

[CobaltSky]

Hmmn, yes, well that does complicate matters.

Depending on how your users normally interact with the solution, you may be able to work around the problem in the short term by altering the formula for the 'cItemSearch' field (as per my previous posting) to:

Case(IsEmpty(Last(gItemSearch), "AllItems", Last(gItemSearch))

Then the relationship which the value list is basd on will always reference the value which is in the lowest/last repetition of the gItemSearch field.

Although not ideal, if users always work down the repetitions from the top, that will give them a workable interface for the most part. The main problem will then be if they want to go back and edit earlier entries.

[MarcS]

Nice... !

Actually, following up on your last thoughts, what do you think about this :

----

Status (CurrentRepetitionNumber) : Returns a number representing the current (active) repetition of a repeating field.Status (CurrentRepetitionNumber) returns 5 when the cursor is in the fifth repetition of a repeating field.

----

So that way, I find out where the cursor is, get the description for that index, and pass it on to the catalog, so that the correct filtering is done. I think it should work...

Update :

I tried it, but Status(CurrentRepetitionNumber) seems to update itself strangely...

If i create a variable CURRENT_REP, and set it as an unstored calculation : "Status(CurrentRepetitionNumber)" , it doesn't update when I click in a repeating field. If however, I click on CURRENT_REP, it sets itself to 1 (normal), and if I then click on a repeating field, I get the current repetition, but then the update stops. Clicking on anything else changes nothing, Unless I click on CURRENT_REP again, and choose another target. Updating via a script & button works well. Any idea why CURRENT_REP doesn't update everytime I click somewhere... ?

[Ugo DI LUCA]

Hi Marc,

How many codes exactly are there in this file and how many repetitions. I agree with Ray that this could be a "short term" solution, but I'd seriously reconsider portals and line item as not only "workarounds".

It will save your time in the "long term" .

I may not have understood the whole "job", but another short term workaround would be to split all your repetitions in a Multi-key calculation field.

[MarcS]

Hey Ugo,

In my catalog, I'll probably have around 100 entries.

In my submission, I have 30 Lines With the following info :

CODE - DESCRIPTION - WIDTH - LENGTH - HEIGHT - QUANTITY - PRICE - TOTAL

Idealy, I put in a code, and DESCRIPTION, WIDTH, LENGTH, HEIGHT, and PRICE are looked up.

My "Would be nice feature" that I am trying to do is make the code field a menu with the filtered codes from catalog, depending on the currently inputed DESCRIPTION.

Just checking if I understand how Line Items work :

To substitue my current submission I'd do :

-create a database with submission line items. Same fields as the lines, + a SUBMISSION# for the relationship.

-When I create a submission, I call a script which creates 30 entries (I want 30 empty lines to show) with SUBMISSION# set to the new submission number, so that they all 30 line items show in the submission page.

Then I do my relations with that.

Would that be it ?

I had actually done that a while back (and decided to go back to repeating fields). What I hate about it is that when I compute the GRAND_TOTAL, I have to loop through all 30 fields and add teh individual TOTALS, instead of SUM(repeating field).

Any thoughts ?

Thanks for all the help by the way. Really appreciated !

Marc

Edit : I hate editting posts, i get double spacing between lines... humm

[Ugo DI LUCA]

Hi,

So you want to auto-enter the products sizes and prices when entering Red Door.

But you want a portal/list with these 2 doors you're selling if you only enter "door".

Well, this would be really useful to get rid of repetitions immediately and look to a relational design, where the sizes and prices could be looked up using the Unique_Id.

If there is a problem using a Unique_Id (as you probably don't know all your id's), you could use a Multi-key to get exactly the product you need.

Have a look to "Multi-key in Action" in the Sample section. Not that this is the best sample, but as I did it, I may be able to help you more on this.

Hope this would help.