Restrict Value List to fields from certain records

[Mandu]

Ok, I'm whipped. Many of the threads here seem to be about filtering, but I'm not getting it....

A Workshops record contains WorkshopName and ActiveFlag. ActiveFlag is a Number field containing 0 if the workshop is inactive, 1 if active.

I want a value list that contains the WorkshopNames for active records.

The ActiveWorkshops value list is defined to be "Use values from field...", "Only related values: WorkshopsByActiveFlag", using values from :Workshop_ID and ::WorkshopName.

The WorkshopsByActiveFlag relationshop is from Workshops to Workshops, a self relationship matching ActiveFlag to ::c_Constant.

c_Constant is a stored, indexed calculation field that evaluates to the number 1.

My layout has a field (g_WorkshopChoice) whose Format is: Pop-up List, using value list ActiveWorkshops.

Clicking on that field produces a pop-up list containing *all* workshop names, including inactive ones.

Am I screwing up in the relationship? It wouldn't be the first time...

Thanks....

Chap

[QuinTech]

The WorkshopsByActiveFlag relationshop is from Workshops to Workshops, a self relationship matching ActiveFlag to :c_Constant.

I think you have this backwards. Try flip-flopping it and see if that helps any.

J

[QuinTech]

Am I screwing up in the relationship? It wouldn't be the first time...

I just got this, LOL (literally!)

[Mandu]

D'oh! I understand.

Thanks, and a tip o' the hat, Jerry.

Chap

[Mandu]

I just got this, LOL (literally!)

It's funny 'cos it's true!

But all waggery aside, I'm still having troubles of the value list kind: it works fine for layouts in the Workshops database, but not for the Students database, where I have a value list defined as "from another file"; that is, from the Workshop value list. Here, it yields an empty list. I may be brain-dead at this point, but is it possible that this value list can't be accessed from another database?

[QuinTech]

I don't think i've ever tried that method. I can see reasons why it might not work, but one would hope it would. Seeing as it doesn't, i'd suggest, rather than using a list from another file, again making it a list based on related values in the same way you did for the Workshops file. Just create c_Constant in Students and link everything up in the same way.

[Vaughan]

I do it this way: create a calculation field "WorkshopNameActive"...

If (Active = 1, "WorkshopName", "")

Create a value list in the Workshops file "WorkshopNameActive" and base it on the calculation field.

No fancy relationships required.

[Mandu]

Wow. Okay, that worked. Thank you once again!

Vaughan's suggestion was right-on: I didn't know null values were omitted from value lists (tho' it makes perfect sense). I'll save that one for next time ;-)

I am incredibly grateful to both you guys (and others) for your generous help, and I hope eventually I can start to give back.

Chap

[QuinTech]

Cool idea, Vaughan. Is this a style thing, or do you find that it works faster than a relationship-based VL?

J

[-Queue-]

Case( Active, WorkshopName ) should work also. No need for the null false result.

[Steve N]

I tried this solution and got a weird result

I have a table called names some records are sold to, ship to or both

I want a list of ship to names only

I created two calculation fields-

ship to ID

ship to Name

The calculations are:

case(not empty(masterID);nameID)

case(not empty(masterID);names)

(when masterID is not empty then the record is a valid ship to)

so far so good, I have 66 records that are "ship to" the first field displays the nameID and the second diplays the name in all 66 records and only those records.

I created the list with the first field the ship to ID, the second field Ship to Name, sorted by the second field

Heres the weird part. My list contains the first and last record only. only 2 items not 66.

[-Queue-]

That would seem to imply that there are only two unique names. Is that the case?

[Steve N]

Each name id and Name is unique in each of the 66 records.

By the way, I am using v3.

[-Queue-]

Try stuffing and attaching a sample of the file. There's something we're not seeing here.

[Steve N]

Thanks in advance for your help. I have been playiing with this file so there is a lot of junk on the layout and many fields that I dont use anymore.

I set up a self relationship between nameID and masterID so the user can create a ship to address and it looks like a related file when its really all one file. When the masterID is not empty then the record is a valid ship to.

I am going to write a script triggered by a button so if the ship to is the same as the sold the script would set field masterID to match nameID. The address would appear in the ship to portal, but it would store only one record.

The last two calculation fields in names file are created to create the list. They seem to populate fine. I did a find and all the valid ship to records have a value in the caculation records.

There is a table called ship to ignore that, I experimented with seeting up ship as a seperate table

I am displaying the list on the adjusment field on names menu. I just picked that field for a quick test.

Let me know what you think

[-Queue-]

ShipToName is defined to return a number result. Change it to text and your value list works.

[Steve N]

I hate it when its so obvious, I thought I check that. Thanks for your help