Jump to content

  •  

Photo

Empty relationship producing "spaces" in field


  • Please log in to reply
30 replies to this topic

#1 jkluchnik  newbie

jkluchnik
  • Members
  • 54 posts
  • LocationLiechtenstein
  • FM Application:11 Advance
  • Platform:Mac OS X Lion
  • Membership:TechNet
  • Time Online: 1d 25m 4s

Posted 01 February 2012 - 02:38 PM

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

#2 comment  consultant

comment
  • Members
  • 24,275 posts
  • Time Online: 334d 2h 48m 54s

Posted 01 February 2012 - 04:33 PM

Can you reproduce the problem in a new file?
  • 0

#3 jkluchnik  newbie

jkluchnik
  • Members
  • 54 posts
  • LocationLiechtenstein
  • FM Application:11 Advance
  • Platform:Mac OS X Lion
  • Membership:TechNet
  • Time Online: 1d 25m 4s

Posted 01 February 2012 - 05:25 PM

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.

Attached Files


  • 0

#4 comment  consultant

comment
  • Members
  • 24,275 posts
  • Time Online: 334d 2h 48m 54s

Posted 01 February 2012 - 05:32 PM

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

#5 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,839 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 231d 2h 27m 5s

Posted 01 February 2012 - 07:18 PM

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, 01 February 2012 - 07:40 PM.

  • 1
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#6 jkluchnik  newbie

jkluchnik
  • Members
  • 54 posts
  • LocationLiechtenstein
  • FM Application:11 Advance
  • Platform:Mac OS X Lion
  • Membership:TechNet
  • Time Online: 1d 25m 4s

Posted 02 February 2012 - 10:08 AM

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:
  • 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 " " & " ".
  • 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

#7 comment  consultant

comment
  • Members
  • 24,275 posts
  • Time Online: 334d 2h 48m 54s

Posted 02 February 2012 - 10:27 AM

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

#8 jkluchnik  newbie

jkluchnik
  • Members
  • 54 posts
  • LocationLiechtenstein
  • FM Application:11 Advance
  • Platform:Mac OS X Lion
  • Membership:TechNet
  • Time Online: 1d 25m 4s

Posted 02 February 2012 - 10:30 AM

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

#9 comment  consultant

comment
  • Members
  • 24,275 posts
  • Time Online: 334d 2h 48m 54s

Posted 02 February 2012 - 10:46 AM

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

#10 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,839 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 231d 2h 27m 5s

Posted 02 February 2012 - 10:47 AM

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
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#11 jkluchnik  newbie

jkluchnik
  • Members
  • 54 posts
  • LocationLiechtenstein
  • FM Application:11 Advance
  • Platform:Mac OS X Lion
  • Membership:TechNet
  • Time Online: 1d 25m 4s

Posted 02 February 2012 - 11:32 AM

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.

Screen Shot 2012-02-02 at 20.19.28.png

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

#12 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,839 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 231d 2h 27m 5s

Posted 02 February 2012 - 12:18 PM

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
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#13 comment  consultant

comment
  • Members
  • 24,275 posts
  • Time Online: 334d 2h 48m 54s

Posted 02 February 2012 - 12:22 PM

Hold on: I believe I can reproduce the issue.
  • 0

#14 jkluchnik  newbie

jkluchnik
  • Members
  • 54 posts
  • LocationLiechtenstein
  • FM Application:11 Advance
  • Platform:Mac OS X Lion
  • Membership:TechNet
  • Time Online: 1d 25m 4s

Posted 02 February 2012 - 12:29 PM

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

#15 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,839 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 231d 2h 27m 5s

Posted 02 February 2012 - 12:50 PM

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
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#16 jkluchnik  newbie

jkluchnik
  • Members
  • 54 posts
  • LocationLiechtenstein
  • FM Application:11 Advance
  • Platform:Mac OS X Lion
  • Membership:TechNet
  • Time Online: 1d 25m 4s

Posted 02 February 2012 - 12:56 PM

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

#17 comment  consultant

comment
  • Members
  • 24,275 posts
  • Time Online: 334d 2h 48m 54s

Posted 02 February 2012 - 01:24 PM

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?

Attached Files


  • 0

#18 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,839 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 231d 2h 27m 5s

Posted 02 February 2012 - 01:38 PM

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.filema...a103f05/summary
  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#19 comment  consultant

comment
  • Members
  • 24,275 posts
  • Time Online: 334d 2h 48m 54s

Posted 02 February 2012 - 01:57 PM

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

#20 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,839 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 231d 2h 27m 5s

Posted 02 February 2012 - 02:25 PM

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
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.




FMForum Advertisers