1 2 Previous Next 23 Replies Latest reply on Mar 2, 2012 11:32 AM by philmodjunk

    Flag that displays ID_Account

    MarcMcCall

      Title

      Flag that displays ID_Account

      Post

      I have 2 tables Account and Contacts, linked by ID_Account and id_account.  On the Contact page I have a field Approvers_Email and another field named Approvers_Email_two.  I have an unsorted spread sheet that I need to import that is going to be imported to match field to the Contacts Email.  The spread sheet will be updating Contacts in 5 different ID_Accounts. The Approvers Email and if there are 2 will be imported into the other 2 fields.  I need to set it up so that I have 2 fields display if either of the the Approvers_Email and Approvers_Email_two are set up as Contacts under a A different id_account.  I would like it to display the account_id the Approvers email is set up as a Contact in.  I then can inform the approvers from other accounts that they need to set up another email account so they can log into the other accounts to approve the web sale orders. 

        • 1. Re: Flag that displays ID_Account
          MarcMcCall

          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?

          • 2. Re: Flag that displays ID_Account
            philmodjunk

            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.

            • 3. Re: Flag that displays ID_Account
              MarcMcCall

              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.

              • 4. Re: Flag that displays ID_Account
                philmodjunk

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

                • 5. Re: Flag that displays ID_Account
                  MarcMcCall

                  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?

                  • 6. Re: Flag that displays ID_Account
                    philmodjunk

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

                    • 7. Re: Flag that displays ID_Account
                      MarcMcCall

                      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.

                      • 8. Re: Flag that displays ID_Account
                        philmodjunk

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

                        • 9. Re: Flag that displays ID_Account
                          MarcMcCall

                          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.

                          • 10. Re: Flag that displays ID_Account
                            philmodjunk

                            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.

                            • 11. Re: Flag that displays ID_Account
                              MarcMcCall

                              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

                              • 12. Re: Flag that displays ID_Account
                                philmodjunk

                                What do you see when you put T08_CONTACTS 2::id_account on the layout? Do you see a value?

                                • 14. Re: Flag that displays ID_Account
                                  MarcMcCall

                                  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?

                                  1 2 Previous Next