Sign in to follow this  
Followers 0

Empty relationship producing "spaces" in field

31 posts in this topic

Posted

I have a relationship that is giving strange results. The relationship is complex, using 4 match fields. Some of these fields depend on other relationships, etc....

The parent table has a calculation field that equals a field in the child table. Everything "seems" to work fine EXCEPT, when there is no matching record from the child table, the result in the parent table seems to be correct, an empty field. However, when clicking in the field, there are 2 spaces at the beginning of the field. We discovered it because when we export this to Excel, there are in fact two spaces at the beginning of the field. This one really has me stumped.

Note:

Say that the Parent Record field that has the problem has the calculation childTable::name. A workaround that fixes this is if I make the calculation Case ( isempty ( childTable::id ) ; Null ; childTable::name ). "Null" being a custom function I have which is just empty. This works perfectly, but just childTable::name produces the two spaces.

One final note: I have just done a test and set conditional formatting on the field to turn it red if the field is empty. It will not turn red. However, If I use the workaround above, then it does. So the spaces are actually showing as data of some kind.

0

Share this post


Link to post
Share on other sites

Posted

Can you reproduce the problem in a new file?

0

Share this post


Link to post
Share on other sites

Posted

Of course you are the first one to reply Comment. I'm so glad you are around :laugh2:

Unfortunately not. Attached is the test file and it works perfectly. When the green field is empty, then it should be EMPTY. In my big solution, it has two spaces in it unless I do the workaround.

Test Empty Records Problem.fp7.zip

0

Share this post


Link to post
Share on other sites

Posted

Well, then the next question is what happens if you save a compacted copy of your file. And the question after that is what happens if you do recover on that copy.

0

Share this post


Link to post
Share on other sites

Posted (edited)

Well when you said it contained two spaces, jkluchnik, I had to check it ...

Look in your CustodianAccounts table at the calculation for Account Name (in your real file where it is breaking in this weird way). It is:

custodianShortName & " " & accountTypeShort & " " & accountCurrency

By any chance ... do you have it unchecked in that calculation dialog, below where it says, "do not evaluate if all referenced fields are empty"? If so then your Account Name can end up with only two spaces. You will want to keep this checked so that, if both those fields are empty, the calculation will not evaluate at all.

I see you have Account Name unchecked 'do not evaluate if all referenced fields are empty' in the GL Entries table. But on that side, it makes no difference because the only field referenced is CustodianAccounts::Account Name and it will never be empty (possibly) because of that checkbox in CustodianAccounts Account Name? It might be worth checking.

BTW, the 'green field' referenced is the CustodianAccounts::AccountName field being discussed.

Edited by LaRetta
1

Share this post


Link to post
Share on other sites

Posted

Hey Comment, I was just about to jump on to try your suggestions. But today, the "You da man" prize goes to LaRetta (or is that sexist? You da gal?). Thanks though for your always useful suggestions.

LaRetta, you were correct that checking that in the child table fixed the problem!

HOWEVER, here is where my ignorance shows. This makes no sense to me for the following reasons:

  1. The custodianAccounts table has NO records that are not complete, including accountName. So, it cannot be the result of pulling an accountName field that has the 2 spaces as a result of the " " & " ".
  2. The only instances where the Parent table should have the accountName table empty is when there is no match in the relationship. Therefore, if there is no match, why would the relationship pull any content of the child record. It is obviously doing so as clicking the checkbox on fixes the problem. I just don't understand why.
0

Share this post


Link to post
Share on other sites

Posted

the "You da man" prize goes to LaRetta

Definitely, but I am just as puzzled as you are - because you insist that no related record exists in CustodianAccounts, while everything else indicates the opposite. What happens if you calculate, from the point-of-view of GLEntries, Count ( CustodianAccounts::accountName )?

0

Share this post


Link to post
Share on other sites

Posted

Hey Comment. If you look at the workaround that I had found, which was from GLEntries, Count ( CustodianAccount::id ), as id is never empty, it did block the two spaces being entered, which verifies that there is no related record.

0

Share this post


Link to post
Share on other sites

Posted

Yes, but you are assuming CustodianAccount::id is never empty. Here's another idea: change the calculation field to =


custodianShortName & " " & accountTypeShort & " " & accountCurrency & " " & Get ( RecordNumber ) & " " & Get ( RecordID )



and see where that gets you.

0

Share this post


Link to post
Share on other sites

Posted

You can verify if there is a related record but do this: Place all the fields from CustodialAccounts onto table layout of GL Entries. Allow entry into all the fields.

Account Name and Custodian Short Name allow the cursor in. But the rest of the fields do not.

0

Share this post


Link to post
Share on other sites

Posted

Yes, but you are assuming CustodianAccount::id is never empty. Here's another idea: change the calculation field to =


custodianShortName & " " & accountTypeShort & " " & accountCurrency & " " & Get ( RecordNumber ) & " " & Get ( RecordID )



and see where that gets you.

Comment, I understand what you are saying about assuming. However, id is set to auto enter as a serial. Nobody has access to that except for me, and I am looking at the full table right now with 120 records and have verified that every one of them has an id.

Nevertheless, your suggestion gets me the following: in account that should have nothing, it gives me 00. In accounts that should be related, it returns the normal account name followed by a 1 and the Record ID of the related account. And this is with the "LaRetta" checkbox off. If I click it on, the 00 in the empty records disappears, as well as the 1 in the ones with the account name.

Sorry about the small image, I cannot figure out how to insert an image inline.

post-83065-0-60895600-1328210941_thumb.p

You can verify if there is a related record but do this: Place all the fields from CustodialAccounts onto table layout of GL Entries. Allow entry into all the fields.

Account Name and Custodian Short Name allow the cursor in. But the rest of the fields do not.

LaRetta, your suggestion allows me to enter fields of CustodianAccount that would be calculated in CustodianAccount but not fields that would be entered at the CustodianAccount level.

I have to be honest guys, I don't understand the significance of the above.

0

Share this post


Link to post
Share on other sites

Posted

The significance of what I suggested is that, if there is no valid relationship, none of the related fields (except any which are unstored calculations or globals) should be enterable. If the relationship IS valid then all of the fields should be enterable. So that is why you can get two spaces in AccountName even without a relationship ... they both are unstored.

I believe the issue is this: You do not have a relationship but you are seeing unstored calculations which are producing the left-over effects when those two fields referenced are empty.

0

Share this post


Link to post
Share on other sites

Posted

Hold on: I believe I can reproduce the issue.

0

Share this post


Link to post
Share on other sites

Posted

I believe the issue is this: You do not have a relationship but you are seeing unstored calculations which are producing the left-over effects when those two fields referenced are empty.

Well, I'm glad to hear that the relationship is not there, otherwise I would have had to question if I should even be programming this as I would clearly not know what I am doing. However, that unstored leftover stuff is kind of scary. We export data from this solution into our fund accounting package. And the fund accounting package was rejecting the data as those fields should have no data. It was seeing the two spaces as us trying to upload invalid Null data. Either way, I'm glad we could resolve it with the checkbox. It still bothers me though that without the check the data is there, because as the relationship is not there, nothing should come across.

0

Share this post


Link to post
Share on other sites

Posted

It IS strange that unstored calculations display and even export their values even without a valid relationship. Let's wait and see what Michael comes up with ... he can pin down the logic on anything. I consider it unexpected behavior also.

You will need to make sure that you do not include unstored calculations that return a result and use checkbox (do not evaluate ...) for Account Name.

0

Share this post


Link to post
Share on other sites

Posted

Unfortunately, I won't be able to avoid the unstored calculations as this is a very complex system. But I will have to be very careful as to the results. But the checkbox does do the trick in this case. I can't wait to see what comment comes up with. But for now, I'm off to bed. Its been a very long day.

0

Share this post


Link to post
Share on other sites

Posted

You will need to make sure that you do not include unstored calculations that return a result and use checkbox (do not evaluate ...)

Funny you should say that, because that's exactly what it took to reproduce the issue. I can't make up my mind if this is a bug or not. I keep thinking: what would I want it to do if it had only Get (CurrentTime) in it?

GhostParent.zip

0

Share this post


Link to post
Share on other sites

Posted

That's how I identified it ... I saw they were unstored and tested an export without a relationship but only Get ( CurrentDate ), LOL.

Yep, your file nails it. What I would want it to do? If the relationship is not valid then values from child fields should not display in the parent (even if unstored or global) and certainly, none should export from child table along with that parent record.

On this one ... I would consider it a bug and it can be serious, as in this case. Of course, that's only my opinion. I think it is important to report it as a bug, jkluchnik. I mean, FM isn't keen on fixing bugs but at least if it is imported on their bug forum, others won't be caught in similar situation ... it gets the word out. You can report bugs here:

http://forums.filemaker.com/hives/1eea103f05/summary

0

Share this post


Link to post
Share on other sites

Posted

Yes, you are right - it should not export. What makes it even weirder is that it does NOT export if you select XML as the format...

0

Share this post


Link to post
Share on other sites

Posted

Strange. Save As Excel & Export FieldContents gives us all fields and data (no surprise there but I had to test). A child record without a parent shouldn't produce anything from the parent side. Appropriately named GhostParent ... :yep:

Of course one could argue that a child without a parent is an orphan and it shouldn't be allowed to happen but this river flows both ways.

0

Share this post


Link to post
Share on other sites

Posted

Thanks all for the input on this. I have to say, this FM Forums community is amazing.

As for reporting the bug, I will do that, but first I have to find the time to write it up correctly. Unfortunately, time is not something I have much of to spare... :hmm:

0

Share this post


Link to post
Share on other sites

Posted

I, or someone else, would be happy to do it for you. :)

0

Share this post


Link to post
Share on other sites

Posted

Please feel free! I seem to be good at finding actual bugs in FM. This is the second one in as many months!

0

Share this post


Link to post
Share on other sites

Posted

I went ahead and reported this bug here http://forums.filemaker.com/posts/c74c241a55

I would have preferred if Michael write it up. Nobody is better at concise, precise wording than Comment but oh well ... the idea is to let people know about the issue BEFORE they discover it the hard way. :)

0

Share this post


Link to post
Share on other sites

Posted

Thank you for doing that. I just read the reporting and think you did a marvelous job. I could never have put it so clearly, covering all the issues and using the correct "lingo". I'm just a lowly exec, pretending to be a filemaker programmer......

0

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0