I'm getting very frustrated with this conundrum

[lkalliance]

New member here, I'm looking for some "relationship advice," lol.

I have a file, in which I record glucose readings for a diabetic. There is a table, EVENTS. Each EVENT has, among other fields, a "Period". Each EVENT is also linked, through a join table, to an arbitrary number of FOOD ITEMS.

The function of this file is for the user to enter in some of the data on his next EVENT, and be presented with data from past EVENTS, filtered and sorted for similarity to the EVENT he's entering now. I've accomplished this by, on the EVENT table, having a series of global fields designed to accept his new data, and once enough fields have been entered in the entry layout it displays (in a portal) this filtered list of past EVENTS for the user to analyze in making his dosing decision.

Now, that filtered list is meant to have two filters...

(1) A required filter that matches the "Period" that the user has entered into the "New Period" global field. This part works like a charm, it's a very simple self-join ("New Period == Period").

(2) Here's the problematic one: I want the user to be able to further filter the list by entering a text string that gets compared to the FOOD ITEMS (and FOOD ITEM CLASSES) and only shows records that not only meet criteria (1) but also has this string.

And here's the frustrating part: I can see that I have it all set up correctly at least partway. I have the dynamic value lists and calculated text fields set up appropriately so that each EVENT has a text field that contains the text of all its FOOD ITEMS and related FOOD ITEM CLASSES. It's all there. There is a calculated field that I used to test to see if each EVENT is correctly determining if it contains the user's text string, and it is working flawlessly.

The problem is, when I add this second criteria to the relationship, I get inconsistent results. Depending on which direction I enter the match fields, I either get no FILTERED EVENTS or I get too many. Worse, though the entry layout contains only global fields and thus no matter which "record" I'm looking at I should see the same filtered list, that's not the case; I'm seeing a different list as I move through different records of EVENT.

I've tried combining the two criteria into one numerical criterium, and I've tried separating out all these global "New event" fields into their own table, but to no avail; the issue persists.

Might I get some wisdom?

[Søren Dyhr]

each EVENT has a text field that contains the text of all its FOOD ITEMS and related FOOD ITEM CLASSES.

What is it 1NF says?? One fact per field, at least is linking on user enterable data following it's own kind of logic - always link relational on internal autoenter serials!

But couldn't you upload a template showing what you're on about?

--sd

[lkalliance]

Sorry about that; getting used to the posting conventions here. I've attached a file.

I spent last night reading some of Mr. Vodka's white paper, and I realize there's a lot of naming and presentation conventions I haven't followed, so I apologize in advance if that confuses anyone. I've probably made things way too complex, too, in my organic way of figuring things out, and for that I apologize as well.

The text field I was referring to was not a match field. All relationships in the file are based on serial number IDs. What I did was create a calculated text field on the EVENTS table that reached out through these relationships and pulled back all the individual text bits I wanted to search on, and concatenate them so I had one big text string locally on the EVENTS record.

The file will open directly to the layout in question. The fields on the left are all global fields. The only one there that's important is the drop-down menu below the date and time, the field is "New Period". That's one match field for picking the records that are displayed on the right pane.

You'll see that no matter what you put there, nothing appears on the right. That is due to the presence of the second relationship criteria, matching the text string you enter in the field at the upper right to this calculated text string I'm talking about.

If you go to the relationship graph, and edit the upper-left-most relationship (between "events" and "filtered events") and remove the second criteria (Is this filtered = Filter test), and then return to this layout, you'll find that on the one criteria it works fine. Pick a "New Period" from the menu, and all EVENTS show up on the right that match that period.

You'll also note two bold-faced numbers on each record. One is "99999" and the other is either "1" or "99999". The left one is "Is this filtered" and the right one is "Filter test". You can see that they are working...if you type in text in the search field and compare that text to the foods listed for each displayed record, when the text string is found those two numbers match; when the text string isn't found they don't match. So I'm frustrated as to why, when I include that comparison as a criteria, that the filter ceases to work appropriately.

tracker.fp7.zip

[mr_vodka]

I spent last night reading some of Mr. Vodka's white paper, and I realize there's a lot of naming and presentation conventions I haven't followed, so I apologize in advance if that confuses anyone.

This is not my whitepaper. It is David Kachel's whitepaper. I merely reference it because I think it is a good read.

As for your issue, I do not understand what you are trying to achieve here with:

If (Position(Food search string; Search for food;1;1) or IsEmpty(Search for food); 99998; 0) +

If (Period = New period; 1; 0)

[lkalliance]

Oh, lol, sorry about the confusion. At any rate, thanks for linking to it...I can see there are some conventions and some good habits I need to get on board with.

OK, as for this:

If (Position(Food search string; Search for food;1;1) or IsEmpty(Search for food); 99998; 0) +

If (Period = New period; 1; 0)

That was an attempt of mine to combine the two criteria into one. I can break it down...

"Food search string" is a concatenated text field that puts together two dynamic value lists and stores them in a text field. The end result is all the FOOD ITEMS related to this EVENT, and all the CLASSES related to those FOOD ITEMS. I want one of the criteria to be if the user's food search request ("Search for food") is contained within that string.

So I just concocted this. If "Search for food" is in this EVENT's "Food Search String", then that field gets a value of 99998. If, further, that EVENT's "Period" is equal to the (global) "New period" then there's an additional 1 added, to total 99999.

Then there's a constant, "Filter test", which is global and set to 99999. I had hoped that this would accommodate a simple one-criteria self-join ("Is this filtered" == "Filter test"). But that isn't working, for some reason I can't figure out.

[mr_vodka]

Is there any particular reason you are using 99999?

If you need it then try something like:

99997 +

Case ( IsEmpty(Search for food); 1; not not PatternCount ( ¶ & Food search string & ¶; ¶ & Search for food & ¶ )

) + Case ( Period = New period; 1 )

[lkalliance]

No reason I used 99999 specifically; just an arbitrary number I often use as a flag...a convention I use so that it's easy to spot.

The calculation is actually working fine; if you change the relationship for "FILTERED EVENTS" and just make it a "show all" relationship, you'll see I've included the fields to show the result of that calculation. Any EVENT record that matches the text string but NOT the period should be 99998; any record that matches the period but not the text should be 1; any record that matches both should be 99999. That part works fine. But when I try to set up the relationship to only show those that are 99999, that's where it fails.

[mr_vodka]

This is not going to work. The calculation is unstored and the child records need to be indexed. The parent key can be a global or unstored calculation but the child key can not be unstored. Because the calc uses your dynamic value lists, it will not be a stored calc. Sorry I should have taken a look at your file earlier.

[lkalliance]

Hmmmmmmmmm. What if I denormalized the dynamic text; stored the value list text in a plain ol' text field as part of the scripts to add a Food Item or a Class? Would that then make the results indexable?