| Author |
Message |
Hammer
Newbie
Joined: 06 Dec 2012
Online Status: Offline
Posts: 10
|
   Posted: 07 Dec 2012 at 5:30am |
here is what i put into the record selection formula editor (based on the actual tables i am using):
{emp_phones.phone_code} = "Home" and
(IsNull({emp_phones_1.person_id}) OR
{emp_phones_1.person_id}} = "Cell")
when i try to save and close it tells me i am missing a ) and the cursor sits between the last 2 }}. when i add a ) in that spot i then get an error that the remining text } = "Cell") doesnt appear to be part of the formula.
also, i created a formula called Phone Type and put in your verbiage: If IsNull({emp_phones_1.phone_no}) or {emp_phones_1.phone_no} = "" then {emp_phones.phone_no} else {emp_phones_1.phone_no}
where do i then apply the formula?
|
 IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 07 Dec 2012 at 6:39am |
here is what i put into the record selection formula editor (based on the actual tables i am using):
{emp_phones.phone_code} = "Home" and
(IsNull({emp_phones_1.person_id}) OR
{emp_phones_1.person_id}} = "Cell")
The bracket that I highlighted in red is causing the problem so you need to remove it. You use the formula to display the phone number on the report instead of using the field. -Dell
|
|
|
|
IP Logged |
|
Hammer
Newbie
Joined: 06 Dec 2012
Online Status: Offline
Posts: 10
|
Posted: 07 Dec 2012 at 6:52am |
|
I pulled out the } and now another error comes up. it says "A Number is required here" and it highlights "Cell"
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 07 Dec 2012 at 6:57am |
Take a look at my original post - don't use person ID when comparing to "Cell", use the phone code field instead. -Dell
|
|
|
|
IP Logged |
|
Hammer
Newbie
Joined: 06 Dec 2012
Online Status: Offline
Posts: 10
|
Posted: 07 Dec 2012 at 7:10am |
got it. I made the change and it looks like it worked; only cell #'s were showing up...but then i tested it by deleting a cell number from a employee leaving him with just a home # and it then removed him from the report totally, it didnt keep him and list his home #.
any other thoughts? (thanks for your help so far)
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 07 Dec 2012 at 7:30am |
When you added the aliased table and linked to it, did you make the link a left outer join? Link FROM the original table TO the aliased table. Right-click on the link and select "Link Options". Change the Join Type to "Left Outer Join". Enforce Join is "Not Enforced" and and Link Type is "=". You said that every person has a home phone number record, even if the phone number is blank. This makes the original copy of the table the "Master". Joining from that to the aliased table with a left out join means that you'll get all of the records in the mast table, regardless of whether there is a corresponding record in the aliased table. So, I suspect that the join isn't set up correctly. -Dell
|
|
|
|
IP Logged |
|
Hammer
Newbie
Joined: 06 Dec 2012
Online Status: Offline
Posts: 10
|
Posted: 10 Dec 2012 at 5:04am |
i did have the link going to the wrong table but even after i fixed it i am still getting only Home on the report. any futher suggestions?
Would it help if i share more info?
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 10 Dec 2012 at 5:33am |
This is difficult for me to diagnose at this point without seeing the report itself. Do you have a DropBox or someplace where you can upload a copy of the report with saved data so that I can take a look at it? -Dell
|
|
|
|
IP Logged |
|
Hammer
Newbie
Joined: 06 Dec 2012
Online Status: Offline
Posts: 10
|
Posted: 10 Dec 2012 at 6:21am |
|
i can email you the report, and give you a spreadsheet or PDF of the results, but if you dont have access to the tables will it help?
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 10 Dec 2012 at 6:36am |
If you turn on Save Data in the File Menu on the report and then save it, the report will contain data that I can work from without refreshing it. I'll PM you my email address. -Dell
|
|
|
|
IP Logged |
|
|
|
