Portal trigger field not behaving

[stefangs]

Hi all,

 

I have a 'dirty' separation model set of 2 files. In the interface file I display a global field where I can enter a number (key field) and the portal below it should update to show relevant records. This sounds so simple I can't believe it doesn't work.

 

What's even more strange: when I look at the data file, of course, I can see the number updating as I enter the key field in the interface file and vice versa. But when I enter the number directly in the data file, the portal in the interface file updates! To be sure, we're talking about the exact same field. Change it in the data file and it works. Change it in the interface file and nothing happens.

 

Any ideas where to look for the answer to this?

 

Thanks,

Stefan

[Matthew F]

Couple of questions:  Is the global field defined on the interface file or on the data file?  How exactly did you define the relationship between the two files?  Can you reproduce the behavior it in a sample file, or provide screenshots? 

[stefangs]

Hi Matthew,

 

all fields are defined in the data file. But I left out an important point. The key field will eventually be a concatenated field of a constant plus whatever I can enter in the filter field. While trying to hunt down the problem, I found that this isn't the problem though. Even a simple expression fails. But the key field is still a calculation like this:

 

filter_field (a global): value ="2"

key_field (calc field): filter_field

 

relationship is (defined in the interface file):

[parent table] key_field = [children table] key_field

 

So I'm trying to match two calc fields. I suppose that's allowed, and, like I said, it works when I update filter_field in the data file instead of the interface file.

 

If this doesn't help, I can try and make a demo. The actual file is too complex to post.

 

Thanks,

Stefan

[Matthew F]

 

 

The key field will eventually be a concatenated field...

A concatenated field?  Do you mean it is a "multi-key" with different values separated by the return ¶ character?  You say that "eventually" it will be concatenated.  It is not yet concatenated, so this is not the problem? 

 

Using a calculated field to define a relationship should work fine.  Making a demo file might be interesting, to see if the issue is reproducible.  I don't see any problem when I try to reproduce what you describe.  

[stefangs]

Hi Matthew,

 

yes, it's not concatenated yet, as I was making the structure simpler to hunt down the problem. The key in the parent file will eventually be something like

 

2|2014 or 2|2013 oe 2|2012

In other words, I can enter a year in a global field and the (calculated global) key will add a 2| to it.

 

The children may have values like

2|2013 or 1|2013 or 3|2013

In other words, any record that does not have a 2| will not show up in the portal. That's the idea, but I didn't get that far, as even entering just a single number and matching a calculated 2 with a calculated 2 failed.

 

I'll try and make a demo later tonight and post it here. Actually, I'm quite curious myself to see if it works, because I sure think it should.

 

Thanks,

Stefan

[stefangs]

So I made a demo that does exactly what I'd like to do eventually. Needless to say, it works perfeclty well. I squinted a lot at my monitor, but for the life of me I can't figure out what I'm doing in the demo that I'm not doing in the real file as well.

 

Thought I'd post it regardless in case someone find it useful.

 

Thanks,

Stefan

[Matthew F]

 

Needless to say, it works perfectly well.

Well at least your plans aren't the problem  You might want to check the details about your field definitions (are they text vs. date, vs. number, type of storage:  indexed, or not?). 

 

It looks as though you're trying to filter based on a month and year.  You could probably do this in a different way, without the concatenated fields. For example, if you have an reference date on your data file, you could extract this with two different calculated fields:  ref_month = Month(ref_date), and ref_year = Year(ref_date).  Then use both ref_month and ref_date as match fields between your interface and data files.    

 

P.S. There was no demo file(s) attached to your post. 

[stefangs]

Well at least your plans aren't the problem  You might want to check the details about your field definitions (are they text vs. date, vs. number, type of storage:  indexed, or not?). 

 

It looks as though you're trying to filter based on a month and year.  You could probably do this in a different way, without the concatenated fields. For example, if you have an reference date on your data file, you could extract this with two different calculated fields:  ref_month = Month(ref_date), and ref_year = Year(ref_date).  Then use both ref_month and ref_date as match fields between your interface and data files.    

 

P.S. There was no demo file(s) attached to your post. 

The field types is a good catch, but they do match. I'm not trying to reference a month, but the year and a status field of the children records.

 

Not sure why the attachment didn't work - I'm trying again with this post. I'll look at it again tomorrow - it's getting too late for me now. I'll report back should I find out what the problem is.

 

Thanks again,

Stefan

demo.zip

[bruceR]

Or ( see attached )

 

Match directly on status and year.

demoBFR.zip

[stefangs]

Or ( see attached )

 

Match directly on status and year.

Nice! I only need status=2 but that's a nice way of doing it anyway. Thanks for jumping in!

 

Stefan

[stefangs]

I've given up trying to get this to work. Part of the problem (though that would have been the next step) is that the key involves a date on a child record, in other words, I would have not been able to index it anyway.

 

So I now resorted to scripting, but ran into the next roadblock, because I'd like to have the find request look something like this:

 

1/1/2014 ... 12/31/2014

 

where the year would be supplied from a global field so I can quickly change the year within the script.

 

I tried 'Set Field', 'Insert Calculated Result' and 'Replace Field Contents' with variations of

 

Date ( 1, 1, Year (GetAsDate (GlobalYearField) ) ) & "..." & Date ( 12, 31, Year (GetAsDate (GlobalYearField) ) )

 

and trial and error versions of omitting the Year expression or GetAsDate.

 

All the dates are shown as ? I remember doing this sort of thing in FM6 before - perhaps the logic has changed (or my memory of it...)

 

Thanks,

Stefan

[eos]

Using Set Field [ Table::dateField ; Date ( 1 ; 1 ; globalYear ) & ".." & Date ( 12 ; 31 ; globalYear ) ] should work.

 

Why not build the statement in a $var and see what it looks like in the Data Viewer?

 

But actually, to search for dates within a given year, you can simply enter the year, and FileMaker will put in the necessary wildcards.

[stefangs]

eos-

 

I finally got it working. There was a quirk in my finding syntax and somehow it kept finding irrelevant records.

 

Greetings from Starnberger See

 

Stefan