Jump to content

Empty relationship producing "spaces" in field


jkluchnik

This topic is 4381 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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
  • Like 1
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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 )?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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:

Link to comment
Share on other sites

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. :)

Link to comment
Share on other sites

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......

Link to comment
Share on other sites

I've just run into what appears to be a similar problem. The scenario is as follows:

I have a database of WORKS ORDERS and a separate database called DAY FILE. Both of these databases are hosted on an FMP11 Server Advanced.

A field called WORKS ORDER::QUOTATION NUMBER (defined as text, indexed) is related to the field DAY FIILE::REFERENCE NUMBER (defined as text, indexed, autoenter serial, can't modify)

In the DAY FILE I have a calculated field called Currency (text, unstored) whose value is determined as follows:

Case(

IsEmpty(Account code customer) and CONTACT NAMES::Country = "USA";"USD";

IsEmpty(Account code customer) and CONTACT NAMES::Country <> "USA";"GBP";

(SALES ACCOUNTS::MultiCurrency = "Y") and not IsEmpty(Alt Currency); DAY FILE::Alt Currency;

SALES ACCOUNTS::Currency

)

The "Do not evaluate if all Referenced Fields are empty" box is ticked

CONTACT NAMES and SALES ACCOUNTS are two other external databases hosted on the same server that have relationships to the DAY FILE.

What is puzzling me is that if the WORKS ORDER::QUOTATION NUMBER is empty, if I reference the DAY FILE::Currency field it returns a value of GBP, whereas I would expect it to return a null value.

If I select records in the Works Order where the QUOTATION NUMBER is empty, I can export the value of the related currency field as Excel, but it doesn't get exported when I use the XML option.

Do you think this is the same bug?

Thanks

Brian

Link to comment
Share on other sites

Hi Brian,

I almost missed this!

I suggest that you create a simple file with only these fields/relationships. If you still get an unexpected behavior, zip it and post it here. It sounds like same issue but I cannot say for sure without a file; I am a hands-on explorer. :^)

Link to comment
Share on other sites

  • 1 month later...

Hey LaRetta. I disagree. You worded the problem very well, pointed them to this forum, and gave them a sample file. You were more than clear.

The answer they gave makes absolutely no sense. "Expected behavior"? Translation: "We know this problem exists and expect that bad result but have no idea how to fix it".

Link to comment
Share on other sites

Well, I see I pointed to the wrong link. I pointed to the bug forum and not the post on the bug forum but you both obviously found it anyway. Here is the post link:

http://forums.filemaker.com/posts/c74c241a55

I suppose I should speak up and try to explain further so they understand but honestly, I can't let myself go there (others can of course). I'm feeling way too happy today. :laugh:

Link to comment
Share on other sites

This topic is 4381 days old. Please don't post here. Open a new topic instead.

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
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.