Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Lookup problems...


Recommended Posts

Hey Everyone,


I've got an irritating problem with lookups from a portal'ed table. I have one layout corresponding to table A that has a lot of fields in it. Two of the fields, date and name, are used as lookup sources for table B which is accessible via a portal in the same layout. In other words, all new records created in table B via the portal should have date and name fields looked up from one record in table A. This arrangement works fine, but i recently discovered that the lookup doesnt always happen because after I enter the date and name fields, if i click directly into the portal to create records there, the date and name fields of table A aren't committed yet. I can solve the problem by simply clicking out of the fields in table A (to some blank space on the layout) before clicking into the portal for table B, but its simply annoying to do. Anyone know a way to make the records commit immediately upon entering data without having to click out of the field?


Thanks so much for your help!


Link to comment
Share on other sites

That seems odd.


You're right, I just checked and it does behave that way. Seems like a bug in FileMaker. Hang on, I'm checking older versions, I don't think it used to behave like that!


EDIT: FileMaker 6 did not behave like that.

EDIT: FileMaker 8.0v3 does not behave like that either. I think it's an 8.5 bug.

EDIT: FileMaker 7 doesn't do it either. Wait a minute, your profile says you're IN Fm7 ??


I'm only able to replicate the behavior you describe in FileMaker 8.5. (Except for the FileMaker 6 test, I used the exact same file for all tests).

Link to comment
Share on other sites

Dang, now I can't get it to behave like that in 8.5 either.




I definitely saw it do it, several times in a row, in 8.5. I guess it got embarrassed and began behaving itself??? weird, most weird.

Link to comment
Share on other sites

I'm using 8.03 advanced now. Just havent changed my profile. I've tested it pretty well and its definately repeatable. The name field is a drop down list and the date field is a pop up calendar. Its not the field format though because I changed it to edit boxes for both and I get the same problem. Everything is fine if I dont click into the portal directly, but if I do click into the portal than the lookup reads the source fields as empty.

Link to comment
Share on other sites

Well I don't know what I was doing differently the second time I tried in 8.5 but I absolutely positively saw that behavior several times over. If I can isolate the conditions that make it happen, I'll sure post about it and let you know.

Link to comment
Share on other sites

This is a known issue. A child record will not lookup from (or through) an uncommitted parent. True for version 7 and above. I don't know of any good cure for this, other that designing the UI so this cannot happen (e.g. create children by script only).


As an aside, it's not quite clear why you need to replicate the parent's name and date in the child - when these remain available through the relationship. I can think of other types of data that needs to be permanently stored in the child in case the parent is modified, but name and date?

Link to comment
Share on other sites

I find that there is often a behavioural difference regarding data commital when using Auto-entered lookups.


Method 1: Auto-Enter using 'Looked-up value' - relatedTable::Field1


Method 2: Auto-Enter using 'Calculated value' - Lookup ( relatedTable::Field1 ; failExpression )


I tend to use the second method as it has for me proved more flexible and reliable than Method 1 although I'm certain that has it's benefits too. Maybe someone more experienced here could summarise the differences more eloquently.

Link to comment
Share on other sites

EDIT: scroll down to midpost, I think I'm onto something here. Well maybe anyway...



Well, for all that I was able to replicate the problem, I can also pretty reliably


• create a new blank record


• enter a date in the local copy of Date of Birth


• without having otherwise exited that field, click into a portalfield, TextField, and type "blah" or "foo"


• click outside the portal, thus committing the portal record, and have the portal copy of Date of Birth properly look up from the parent record.



Now, to me that makes sense, not because the child record should be able to look up from an uncommitted parent record value but because (it seems to me) the very act of clicking into a portalfield causes the parent record to no longer have the cursor in any of its fields, which I'd think would prompt the committing of the parent record.


Or maybe not, but certainly SOMETHING's happening reliably enough.


So whatever the screwy-hell is going on here, we need not only an explanation for why the portal version of Date of Birth would fail to look up, but also why it would successfully do so.


I may have to make a video screencapture so y'all can watch my cursor movements or something. I sure can't figure out what I'm doing different when the relevant date field looks up as opposed to when it fails to do so.


::goes off to experiment::


MMkay...maybe got something here... in this little test file I've been playing with, the local field that I'm using for the relevant portal relationship is a number field but I didn't bother to set it to auto-enter serial number. I was just manually typing in any old numerical string.


Situation A: Create new record from scratch, click into the ID number field, type in a unique numerical string, then, without committing the record yet, click into the Date of Birth field, enter a date, then (still without committing the record) clicking into portal and typing any old text string in TextField, thus prompting the creation of the related record. Outcome: Date of Birth in portal does not look up


Situation B: I create a new blank record, type in the ID Number, do something to commit the local record like click outside the field, then enter the Date of Birth, then, without re-committing the record, clicking in the portalfield Textfield, entering random string, again prompting the generation of the related record. Outcome: Date of Birth in portal row does look up Date of Birth from parent record.


Or, alternatively (and dependably), go to an existing record, click into Date of Birth field, clear it, commit the record. Record has ID Number but Date of Birth is again empty. Click into local table's Date of Birth field, enter a date, then, without exiting the field otherwise & therefore not committing the record again, click into portal field Textfield, enter random string. Outcome: Date of Birth field reliably once again look up Date of Birth from parent record.


In light of that: Chrisf776, what are the fields used in the portal relationship? Would you be doing all the data entry in Field A (including the Date of Birth field) without ever having moved the cursor out of the data entry fields since the time of record creation, as of the time you click into your portal to enter the portal record?


If so, try using a New Record script that creates the new record, commits it, then puts cursor in the first field in your tab order. Should look/feel the same as simply going Command-N (or Control-N for PC), but if I guess right, it should solve your portal lookup prob, insofar as that should replicate the situation I have in my test file when I already have a committed record with ID Number.


(Worth a try anyway, yes?)

Link to comment
Share on other sites

653 words, and it comes down to this:


A child record will not lookup from (or through) an uncommitted parent. True for version 7 and above. I don't know of any good cure for this, other that designing the UI so this cannot happen (e.g. create children by script only).

42 words.
Link to comment
Share on other sites

it will not work unless BOTH parent and child are committed.


Well Method 2 seems to work OK for me


"Auto-Enter using 'Calculated value' - Lookup ( relatedTable::Field1 ; failExpression )"



I have a layout with the auto-save set to off i.e. any records changed in either parent (master table for the layout) or child (through a portal placed in the same layout) will commit only if I click 'Save'. If I enter data as line items in the portal, lookup occurs immediately (using above method 2). I can enter as many line items as I like, all will lookup OK. I then click outside the portal and choose 'Don't Save' - the portal is returned to it's original state without committing any records.


I agree that neither method will lookup from an uncommitted parent though.


Re: Chrisf776's original post, clicking into a portal field won't necessarily commit data edited in the layouts master table anyway, certainly not in FM7. Theoretically you should be able edit fields from as many different tables as you like in one go if they are all present on the same layout. Only by clicking on a non-data entry area or initiating a record committing action like a script step or layout change would any modified data in all these related tables be committed.

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Create New...

Important Information

Terms of Use