| Author |
Message |
Ariel
Newbie
Joined: 10 Jun 2010
Location: United States
Online Status: Offline
Posts: 33
|
  
Topic: need help with record selection formula
Posted: 16 Nov 2011 at 7:44am |
We have invoices that pull company information but the Contact name and email is the Primary contact that is attached to that particular company.
So now we would like to pull the Billing contact and if there is no billing contact pull the primary contact.
I have no idea how to put this into a formula. Right now it looks like this:
{TCIA_Individual.KEY_CONTACTS} like "*PRIMARY*"
I want to be {TCIA_Individual.KEY_CONTACTS} like "*Billing*" else "*Primary*"
Can someone help?
Thanks!
Ariel
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 16 Nov 2011 at 9:36am |
I think you need to explina your data structure a little more?
Are each address on a different line of data?
Are you doing any joins from company to a contact information table?
|
| IP Logged |
|
Ariel
Newbie
Joined: 10 Jun 2010
Location: United States
Online Status: Offline
Posts: 33
|
Posted: 16 Nov 2011 at 9:44am |
|
Each Company is on a different invoice. There isn't a problem with my joins I just don't know how to do look for Billing contact first and then, if billing contact is null, pull Primary contact.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 16 Nov 2011 at 9:56am |
is the Billing contact and the Primary contact on the same row of data?
if so, then it works something like this...
if isnull(table.billingcontact) or table.billingcontact="" then table.primarycontact else table.billingcontact
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 16 Nov 2011 at 10:13am |
|
Also what I am suggesting is a formula built in the Field Explorer, not a record selection formula.
Edited by DBlank - 16 Nov 2011 at 10:14am
|
| IP Logged |
|
Ariel
Newbie
Joined: 10 Jun 2010
Location: United States
Online Status: Offline
Posts: 33
|
Posted: 17 Nov 2011 at 2:47am |
it doesn't work like that. The Key_Contacts field contains many contact types (example: Billing, Primary, EX could potentially all be in the field seperated by a comma). That is why I have to do a Like statment because they could also be in any order.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 17 Nov 2011 at 3:56am |
Can you please more fully explain your tables/data. I am not 'seeing' what your issue or design is.
The select statment
{TCIA_Individual.KEY_CONTACTS} like "*PRIMARY*"
simply checks for the string of PRIMARY in the Key_Contacts field and if exists includes the row in the data set.
YOu can have it check that field for another strings (e.g. like ["*PRIMARY*','*BILLING*'] ) but it does not explain to me what you are trying to do with different address insertion.
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 17 Nov 2011 at 4:11am |
just my 2 cents...since I rarely if ever find fault with DBlank's solutions... are you joining to the tcis_individual table? If so, you could make it an outer join to the invoice table. This should allow you use the formula that DBlank suggested with checking for NULL and you would be able to use your filter in the record selection to limit the records to only Billing or Primary, since those would be the only records you wanted to use. HTH
|
| IP Logged |
|
Ariel
Newbie
Joined: 10 Jun 2010
Location: United States
Online Status: Offline
Posts: 33
|
Posted: 13 Dec 2011 at 6:50am |
ok, just coming back to this report and I still haven't figured it out. Any other suggestions because these did not work :(
Let me try and explain again...
we have an invoice that pulls company information and then a sub report pulls the Primary contact's name and email.
TCIA_Individual like '*primary*' - this field is a character field and only needs to contain the word Primary. What I want it to do is look at that field and if it contains the word Billing than list that person's name - if Billing is not listed then it should look for the word Primary.
Does that help at all? Sorry, I don't know how else to explain it...
|
| IP Logged |
|
comatt1
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 337
|
Posted: 13 Dec 2011 at 7:02am |
sounds like your focus would be billing, so in the subreport just have select expert only display if that key field is like "*billing*
Then list all the individuals and suppress any that aren't 'Primary'
|
| IP Logged |
|
|
|
