Odd Child Value

[LaRetta]

I've had this need several times. I've attached a file to make it easy.

Need: One parent has several children. If the account number on any of their children is different than the rest, I want to produce an error (unstored calculation) in the parent. I do not know what the account numbers may be so I can't hard code for it. I don't care how many different Account Numbers there may be ... error is error and only ONE different produces the error. All I will know is that EVERY child should share the same account number.

I've accomplished it in various ways in the past that I'm too embarrased to explain. I'd like to find an easy, sweet way to do it (hopefully without putting a new calculation field in the child table since the child table belongs to another company).

Ideas?

Update: Okay, I've used ValueListItems() and if value is greater than 1. But I don't think I should need to create a value list just to get the value; particularly with newer versions of FM. I wish we had a new function where we could define a value list within a calculation, something like:

CreateVL ( table ; field )

Then we could use something like: CreateVL ( child ; AccountNumber ) > 1

It would evaluate depending upon that popup at the top of calculations. How simple that would be.

ValueCheck.zip

Edited February 9, 2008 by Guest

[comment]

Try:

Let ( [

all = List ( Child::AccountNumber ) ;

fv = FilterValues ( all ; Child::AccountNumber )

] ;

ValueCount ( all ) ≠ ValueCount ( fv )

)

This assumes there are no empty values in Child::AccountNumber, so perhaps this would be better:

Let ( [

all = List ( Child::AccountNumber ) ;

fv = FilterValues ( all ; Child::AccountNumber )

] ;

Count ( Child::ParentID ) ≠ ValueCount ( fv )

)

[LaRetta]

I didn't even think about a blank value being an error, Michael, thanks for catching that possibility. I don't know if Account Number could ever be blank but your second calc accounts for it nonetheless. And of course it works perfectly. I danced all over with List() and ValueCount() but missed the logic completely. Thank you - I will be using this in several places in this solution and in many other solutions! :smile2:

[LaRetta]

I've been asked to change the requirement.

If we can't do it without modifying the child table/file then I will tell them it can't be done. They have exceptions they just identified:

If Account #180003 and Account 222000 are the only two accounts, it should not be considered as a break in process. I asked for confirmation that this is the ONLY exception and I was assured it was. Somehow, if possible, I'd like to plan that they are wrong because I've the feeling exceptions are the norm.

1) Is there a way to 'mate' two account numbers (regardless of what they are) so they are treated as ONE account? From child side, I could see creating a new calc substituting one for the other but from the parent side within your calculation?

2) Is there a way to plan for their inconsistency in the future by allowing us to mate accounts from User browse level for admin control instead of hardcoding within a calc?

Well, I thought I'd ask ... your consideration would be much appreciated, Michael.

Edited February 21, 2008 by Guest

[LaRetta]

I was thinking ... yes dangerous. But I suppose I could create an identical calc which used these two account numbers and if calc2, not calc1, ie, if calc2 has a value then make calc1 null. But I think it would break if there were three different account numbers.

[comment]

Do I understand this correctly:

The general rule: all child records must have the same account number - else flag an error.

The exception: do not flag error if ALL (?) child records have account number A or account number B (or some other number from a given list of "special" account numbers).

This point is not clear enough: if all child records have the same account number (a "regular" one, not one on the special list), except some that have special account numbers - is this an error to be flagged?

Also, is it possible to keep the list of special account numbers in a table, or in a multi-line field in Preferences (or a global field)?

[LaRetta]

1) The general rule: all child records must have the same account number - else flag an error.

Correct, unless exception.

2) The exception: do not flag error if ALL (?) child records have account number A or account number B (or some other number from a given list of "special" account numbers).

I think examples would be required. Let's assume we have mated two sets of account numbers: 180003 goes with 222000 and 631150 goes with 934777). I do not believe it would ever involved more than 3-4 exception mates such as this. Then here are situations:

180003

180003

180003

... pass ... count is 1

180003

222000

... pass (exception ... counts as 1)

222000

222000

... pass (all same ... counts as 1)

180003

170800

... fail (counts as 2)

180003

170800

222000

... fails (counts as 2 )

631150

631150

180003

... fails because, even though they both are in exceptions list, they are not mated). So mates should count as a single entity (instead of 2) but if not with their mate, they count as one anyway.

3) Is it possible to keep the list of special account numbers in a table, or in a multi-line field in Preferences (or a global field)?

Yes. It will be in served solution which will be administered by a User. There is already a Preference file with one record which could be used to hold the values. I'd prefer not to use global because I don't want them to unserve it just to change a value.

I hope I addressed all of your questions. If not, I am here. And thank you.

UPDATE: An error would flag if count greater than 1 'entity' and mated pairs would count as 1.

Edited February 21, 2008 by Guest

[comment]

OK, I see (hopefully). So these are not special accounts, only some accounts have "siblings" and each group of siblings needs to be counted as a single account.

Is it possible for you to do this in the correct Filemaker way? That is, to have a table of all Accounts, where each account would have a unique number, and in addition there would be this field called PrimaryAccount. This value would be the same for all siblings in a group. This would enable us to have a relationship to the Accounts table based on:

List ( Child::AccountNumber ) = Accounts::PrimaryAccount

Anything that has more than one related record in Accounts is an error.

Sorry, that was a mistake. I meant that ValueCount of List ( Accounts::PrimaryAccount ) needs to equal 1.

Edited February 21, 2008 by Guest

[comment]

Sheesh, what's wrong with me today? I meant the right thing, but managed to write something else - twice. I meant, of course, we'll test for a single unique value in List ( Accounts::PrimaryAccount ) in a similar way to we did above for List ( Child::AccountNumber ).

[LaRetta]

Yes, it will work using another table which can even be within the same file and I've included it in this example. But I'm unsure how to popuplate the table because, even though 222000 is a sibling of 180003, it is NOT a sibling to other account numbers so it should be a primary Account number as well so that it will count as 1 in other situations, right?

I tried many relational configurations and probably 5-6 different calc combinations and I'm sorry but I still can't get it. Would you help me understand what I have wrong? I've attached the file with Accounts table populated to assist you if you wish.

ValueCheckMOD.zip

[LaRetta]

Oh so sorry, I just saw your message after posting - I was working on understanding this and didn't check our thread again. I will consider what you said and try to resolve it. Thank you!

Edited February 21, 2008 by Guest

[comment]

Here's a rough sketch of what I had in mind. The downside of this method is that all account numbers need to be recognized. If a child has an unknown account number, then there will be no primary number for this account, and the test will fail. If this is a possibility, we'll have to think up something else.

ValueCheck2.fp7.zip

[LaRetta]

Hi Michael,

This has taken a bit. My stubborn nature insisted that there must be a 2 for primary as well or it would somehow break. I had to create every configuration and test the heck out of it before I believed it ... not that YOU weren't believable; I should say before I UNDERSTOOD enough to believe.

I understand using a 'group' field. I even understand adding it to a table to create a 'parent' in a flat-type structure. But to then use the group as a filter for another table in this way is pure elegance.

Thank you for sticking with me on it and, as always, it works perfectly! :waytogo:

[comment]

You SHOULD test thoroughly (and especially today's produce...).

I just thought another way to do this would be to have a field in the child table lookup the "parent" (or rather "senior sibling") account number - or use its own in case of lookup failure. That way, you would only need a small translation table for the "junior sibling" account numbers, and go back to the first method to check for uniform value across the list.

[LaRetta]

The downside of this method is that all account numbers need to be recognized.

I didn't clarify but I assume you know we'd be safe if I didn't balk ... there will ALWAYS be an account number and it will always be valid. This is ONE dependable thing from the incoming - the Account number is validated.

But even if it weren't, the whole calc won't break - it just won't recognize the blank entry as a Account so it won't be included in the test (at least that's what it appears to me). And that makes sense (I suppose) except ... how can it have a relationship to Accounts without an Account Number? If I put Accounts::Primary and Accounts::AccountNumber and Parent::cCheckUnique on the Child table, it DOES show a relationship to Accounts even if Child doesn't have an Account number. This appears strange to me, indicating a relationship. But the graph shows relationship from:

Parent::cAccountNumbers = Accounts::Account

Is this that difference in handling relationships we've all been reading about? Or have I had a bit too much of Royce' joy juice?

UPDATE: I mean in 9.0v3, I think, that blank will count again?

Edited February 22, 2008 by Guest

[LaRetta]

Oh! Ha ha! I was so busy testing and contemplating and responding that I didn't see your last message! Well, remember we don't want calc or anything in Child; they want child left alone.

Nonetheless, this rocks. But I will re-read your last response to consider what you are saying because it will still teach me quite a bit; particularly now while I'm in the coal face of it.

Test thoroughly ... "Today's produce" :giggle:

Edited February 22, 2008 by Guest
Added belly laugh

[LaRetta]

Ooops.

I'm looking from child through ParentID to Parent. From Parent cAccountNumbers to Accounts and seeing the first related (parent) account number. DOH.

Edited February 22, 2008 by Guest

[comment]

how can it have a relationship to Accounts without an Account Number? If I put Accounts::Primary and Accounts::AccountNumber and Parent::cCheckUnique on the Child table, it DOES show a relationship to Accounts even if Child doesn't have an Account number.

A child has a parent as long as it has a ParentID - even without an account number. And the parent has SOME related accounts as long as at least ONE of its children has a valid account number. So if you put a field from Accounts on a layout of Child, it will show data from the FIRST related record in Accounts - as viewed from the POV of Parent.

we don't want calc or anything in Child; they want child left alone.

That's too bad - 'cause it's a LOT simpler this way.

ValueCheck3.fp7.zip

[LaRetta]

Yes, it sure is simpler! And a great technique to understand!

This program will be attaching to many programs from other businesses and it will be cleaner if we keep it, uhm, cleaner (from their perspective). Nonetheless, I will be using this technique (in both of its forms) often. :smile2: