bump - maybe posting on a saturday evening is not the best way to get a response to a seemingly difficult question?
You are almost there... You need to have a active filter in your relationship as well... currently it seems you are connecting via pk... which is good... just add an active filter between the relationships and you should be good...
ie... active -- active and fk -->pk as well... you could have active =1 in both.. If you put someone as In Active just put a 0
I took the week end off to finish a database project. It happens to be a tutorial file on the subject of conditional value lists--including "hardwired" conditional value lists that only list values from records meeting specified criteria. And if the field that identifies a record for an active group is an unidexed calculation field, you might use it with ExecuteSQL to produce the conditional value list. These are specific topics demonstrated in this file with extremely thorough documentation on how the examples were set up.
See my FaceBook Page for a download link (the file is free):
Due to a noxious forum bug, please protect yourself with a "Select-All, copy to your clipboard" action just before submitting a private message or comment to this forum. The bug can lose your comment and log you out of the forum--forcing you to sign back in and re-enter the comment or message. By copying to the clipboard before posting, you can re-enter your message by pasting from the clipboard instead of having to retype it all over again.
I'm going to attempt to try this, using the logic of a Value List; and hope it works :-/"VL" is "Value List""TO" is "table occurrence", on the Relationship GraphThe VL is 'from' "j_ParticipantGroup" (TO). (Though it is seen in a portal on a "Participant" (TO) layout.)Field (with value): ParticipantID(Will be created automatically when you click into the portal, or via a "new" script)Field (with VL): GroupID(With the VL, with drop-down list of groups with an active program)Field (new): z_c1, calculation, 1, (number)(Just a 1, for all records, can be Indexed or Unindexed)Relationships (new):Programs_activeGroups_activeRelationships:j_ParticipantGroup::z_1 = Programs_active::Active(Targets only the "active" programs)Program_active::Groups_active::GroupID(Sees only the groups with an active programValue List:Field: Groups_active::GroupIDFrom (TO): j_ParticipantGroupI also did not name the new fields in the way I would, as I use full names (so not to confuse with other "original" TOs); and sometimes at "_VL" at the end, if the TO was created just for a VL.(Which could be a good reason to use SQL instead, as Phil showed; but I know little.)P.S. I hope this is mostly correct, as I cannot read very well, and I imagine it doesn't sound so good either.
You've got a problem here. Data in Programs determines whether or not a Group is Active. This mirrors the issue addressed on the ExecuteSQL controlled Value Lists example. A calculation field in Groups can show that the group is active but since it uses data from Programs to do so, the result in an unstored calculation field. This field cannot be indexed and thus cannot be used as the needed match field to a constant in your j_ParticipantGroup table.
The ExecuteSQL method avoids this issue by using a Query to pull up a list of Primary keys from the table serving as a value source for the conditional value list. That field is then used as a match field in the relationship specified to make the conditional value list work.
The Value List is going from ParticipantGroup (TO) to Program (TO) first, from a 1 into Active. This will hit ONLY the Programs "active" found set. Then it just goes to Group via its ID.
This is the result, showing the Value List, with this data:
First, one thing I should have done, in my file, and its picture of the Value List result, was to show the 2nd field in the VL; especially because I named the ID fields as just "ID"; hence my "look, it shows 2 and 3, but not 1" should have shown "Group 2" and "Group 3", not "Group 1" (which has no Program "active"). I've changed my VL to that, and yes, it now visually works.[P.S. Removed a not so good 2nd try. The VL only worked if you already HAD an entry of any Group ID in the table; which is funky (at best). So back to the post before.]
[wonderful - didn't learn my lesson the first time this will be my second time writing this post!]
First off, thanks to both of you!
Sorry for the slow reply, I only really have time to work on this from Friday to Monday, I took this project on as a favor to my research team because I have some data driven web app experience, working weekends is the reward for being nice - hey, I like learning new things :)
Fenton, I might try your solution if all else fails, but I've already put some work into the executeSQL example and I want to see if I can get that working - I've run into some trouble.
Here's what I've got so far:I put the ExecuteSQL command in a field on the j_PraticipantGroup table called cActiveFilter. It reads as follows:ExecuteSQL ( "SELECT \"_KpGroupID\" FROM GroupWHERE cActive_programs == 'Yes' " ; "" ; ¶ )I haven't even tried to make the relationship yet, for the purpose of testing I'm displaying the value from the executeSQL calculation in a layout based on the j_PraticipantGroup. Instead of getting the expected ID number I'm getting "?"sWhat am I doing wrong here?Two quick notes for clarity:
- I've got a calculated field in my Groups table that copies the value from active over. Perhaps this is overkill? I already had it set up so I thought I'd use it, otherwise I think I'd need to use a sql join?
- less important, but mentioned for the sake of pride, I'm using "Yes" and "No" for my active flag because I'm a total FMP noob and didn't bother figuring out how to have "Yes/No" radio buttons display while storing the values as "0/1".
I've been messing around and doing some research. This article was very helpful:
"FileMaker SQL Nightmares: Tips for Debugging SQL Queries"
As was this:
It shows you how to set up a "SQL" table where you can input SQL commands via a layout and see the results - very helpful.
I've changed the query around a bit (ever so subtly):
SELECT \"_KfGroupID\" FROM Groups
WHERE NameActiveList = 'Yes'
A few notes:
- notice what was Group is now called Groups - "Group" is a SQL reserve word.
- Also, notice that I'm using _KfGroupID - This is actually really confusing me - I'm just not sure what Phil's getting at with the escape characters.
- In this iteration It's set up to take the record's foreign group ID (_KfGroupID) - I'm not sure if this is right, I'm still getting '?'.
- I've also tried it as it's set up in the example, using Kp_GroupID (changed because you may not use a field that starts with an underscore (or at least that's what the above link states). That solution always returns "1" in every record - that can't be right?
- Finally, I replaced the double equal (==) with a single equals sign (=).
When I run this in the SQL layout it works - I think... it returns a list of values and seems to match the description in Phil's example doc. But when I plunk it in the j_ParticipantGroup field and give it a spin in my j_ParticipantGroup test layout and I get the '?' or the '1' depending on the set up.
Any idea what I'm doing wrong?
Okay - I got the value list working.
I skimmed over this line a few too many times in Phil's example file (which is awesome, btw):
When a field holds a list of values separated by returns it will match to any record in the related table where any one of the listed values matches to the value in the related table's match field.
The value list is empty when there's no record for the j_Participant table for some reason... but when a record is there - the drop down list works. I can add an ID and the drop down list will appear (stages of filemaker grieving)
This seems like a separate issue - I'll try to figure it out if not I'll make a new post.