I have part of this working, if someone can point me in the right direction. I made the relationships in the graph. I created 2 more copies of the contacts table and made the Approvers_Email = Email and id_account ≠ id_account. I with the 2nd copy of contacts id did the same thing for the Approvers_Email_Two. It is working, but the flag displays on the Contact who is approving for someone in another account, and I would like it to work the other way arround. I would like the flag to pop up on the contact who has an approver in another account. And how can I make it display the approvers id_account, or account name?
If you set up a related table of approvers email addresses, you'd get a bit more flexibility and the option to have more than just two such email addresses without needing to add another field to your table, but let's go with the two fields for now...
If I read this correctly, you have a newly imported contact record where you want to see a flag appear if there is a related record with a matching email in either of the two email fields but only if the account ID does not also match.
Is that Correct?
If so, let's do a bit of relationship trickery to make this match on fields in either email field.
Define a calculation field, cEmailList as: List ( Approvers_Email ; Approvers_Email 2 )
Set up this self join:
contacts::id_account ≠ contacts 2::id_account and
Contacts::cEmailList = contacts 2::cEmailList
This will match to contacts records with a different account ID if an email address in either field of contacts matches to either field of contacts 2.
This works as a two way relationship, any flag calculation will appear for both contact records.
"...The flag displays on the Contact who is approving for someone in another account, and I would like it to work the other way arround."
We'll need to distinguish between the two types of contacts and I don't know enough about your data to suggest a way to tell the difference. Once I know that detail, we can incorpoarate an added check in the flag calculation so that it now longer appears in both cases. How do you distinguish between a contact that is approving for someone else and a contact that is being approved by someone else? Seems like you need different email fields to document approver and approved by emails separately.
Ok, I have this set up. Does cEmailList need to be a text field or number field. I have it set as a text, just to try it out, I copied an email from another account and the email address apeared in the field.
If i'm understanding you correctly you need to know if I have something set up on the Contacts page that says they are an approver or not. Right now, no. Although I can set up a field that I can re-import the list I have, becuse I do have an unsorted list that has the approvers email address in 1 collumn. I could create a field to import that collumn to match the email to get it in the right place, then use that field as the flag. If there is no email in that field, they are not an approver.. Will that suffice, or would you suggest something else.. I think that might be the easyiest way for me to distinguish if a person is a Approver on their record.
cEmailList should return text as you have a list of values separated by returns. (And your email addresses are text anyways.)
I'm looking for a way to tell when you link two contacts in this manner, which is the approver and which is being approved. There's any number of ways you can flag a contact as an "approver". For this example, let's assume there's a field that is empty for "approved by" contacts and contains data for "approver" contacts...
If ( IsEmpty ( ApproverFlag ) and Not ( IsEmpty ( Contacts 2::id_account ) ; "Put warning flag message here" )
Ok, I'm not sure I understand 100%. I do have a field on the contacts page that I enter the "Approvers" email in this would be the "approved by" field. Does this calculation go into another field, and how is the ( ApproverFlag ) in the calculation set up?
You can set up ApproverFlag any way you want as long as it contains some kind of data for approver contacts and is empty when the contact is not an approver. If you have a field that identifies people who are not an "Approver" by putting an email address in an "approvers" field, then you can write the expression this way:
If ( Not IsEmpty ( approvers ) And Not (....
If ( IsEmpty ( Approvers_Email ) and not ( IsEmpty ( T08_CONTACTS 2::id_account )) ; "Warning Message" ) This is what I set up in a field called Flag_approvers_Account. I placed an email address from another account in "Approvers_Email" and nothing happened.
Does an email address in Approvers_email indicate that this is a person is IS or IS NOT an approver?
Do you want a flag to appear for Approvers or for those being Approved?
(Double checking my understanding here.)
The amail address going into Approvers_Email is the person who will approve the order. It is entered on the persons record that they will be approving for. Right now I didn't see a anything pop up on either side. I would like it to pop up on the person who is being approved for, becuase the approver will be approving for multiple people. If it shows on the approvers page I will only see the one flag, as to if it is on the persons page that is being approved for, I can see the flag in every instance.
The amail address going into Approvers_Email is the person who will approve the order.
Then this field should not be empty unless the contact is an Approver. Looks like you need to add a "Not" in front--just like my last example if you only want to flag people who are not approvers.
I caught that after I sent the reply, it isn't showing any type of flag with either If( IsEmpty or If( Not IsEmpty It doesn't display anything if there is something in the field or not in the field "Approvers_Email" on either the approvers side or the being approved side
What do you see when you put T08_CONTACTS 2::id_account on the layout? Do you see a value?
I did try this, I put the approvers email in the "Approvers_Email" on the record they are approving for. I then went to the approvers record and also placed their own email in there own record as thier own approver and the warning popped up. The T08_CONTACTS 2::id_account then displayed the id_account. Is there a way to refine this so I do not have to put the approvers email in the Approvers_Email field on there own page, they may actually have an approver too?