Query that excludes records-Fetch XML limitation?

I posted about this a few weeks ago, but I got sidetracked for a while. I 
haven't been able to figure this out yet, and I'm starting to think it may 
not be possible. I am looking for any and all creative suggestions. TIA.

My Sales users need a way of finding leads that have not been contacted 
since x date. For example, they want a quick way to lookup all leads with no 
phone call activities between 11/1/2004 and today.

I tried c360 SearchPac and no matter how I build a query, it returns all 
leads with phone calls that fall outside of the dates I have selected, 
regardless of whether there is a call during the timeframe in question. In 
addition, it lists a lead every time there is a phone call outside of those 
dates, so if there were 20 calls, the lead is listed 20 times.

My next attempt was at building a Crystal Report with parameters. I am 
basically having the same problem. If a lead has a call today it shows up -- 
apparently because it also has calls before 11/1/2004.

I haven't been able to get it to work right yet. I am stuck. Any ideas? Is 
this a limitation of Fetch XML? Here's the query that I built in Crystal:

<fetch version="1.0" output-format="xml-platform" mapping="logical">
<entity name="lead">
<attribute name="fullname" />
<attribute name="statuscode" />
<attribute name="ownerid" />
<attribute name="leadid" />
<attribute name="firstname" />
<attribute name="lastname" />
<attribute name="description" />
<order attribute="fullname" descending="0" />
<link-entity name="phonecallactivity" alias="phonecallactivity" to="leadid" 
from="objectid" link-type="inner">
<attribute name="actualend" />
<attribute name="objectname" />
<attribute name="ownerid" />
<attribute name="scheduledend" />
<attribute name="title" />
<attribute name="activitytypecode" />
<filter type="and">
  <filter type="or">
   <filter type="or">
    <condition attribute="actualend" operator="lt" 
value="2004-11-01T00:00:00" />
    <condition attribute="actualend" operator="ge" 
value="2004-12-22T00:00:00" />
   </filter>
  <filter type="or">
   <condition attribute="scheduledend" operator="lt" 
value="2004-11-01T00:00:00" />
   <condition attribute="scheduledend" operator="ge" 
value="2004-12-22T00:00:00" />
  </filter>
</filter>
<condition attribute="activitytypecode" operator="eq" value="137" />
</filter>
</link-entity>
</entity>
</fetch>



-- 
Matt Wittemann
http://icu-mscrm.blogspot.com
0
Utf
12/21/2004 8:55:02 PM
crm 35858 articles. 1 followers. Follow

0 Replies
807 Views

Similar Articles

[PageSpeed] 59

Reply:

Similar Artilces:

Filtered Form Query
I have a form that is filtered using Allen Browne's ajbFindAsUType module (http://allenbrowne.com/AppFindAsUType.html). I want to now create a query that utilizes only the filtered records as its source in order to perform a computation. Is there a way to pass the filtered Recordset into a query via a command button? You can use the RecordsetClone of the form to step through the records. You may be able to pass the Filter from the form on to whatever other operation you need to perform. For example, you could create a report that summarizes the data, and open it like ...

The active schema does not support the element 'xml'
I am trying to insert an xml data island into a htm file that I am using for a help file. When I insert the lines of xml, the <xml> tag is underlined in red in VS2003 and the message says "The active schema does not support the element 'xml'. the html is as follows <HTML xmlns:MSHelp="http://msdn.microsoft.com/mshelp"> <HEAD> <meta name="vs_targetSchema" content="HTML 4.0"> <TITLE>Setting Options</TITLE> <xml><MSHelp:Keyword Index="K" Term="Random" /> <MSHelp:Keyword In...

Run a query based on user input to a text box
I am creating a database to manage Auction Bids for a live radio station phone auction weekend. I have a form where the user is entering names as the bidders call in. I want to be able to have entry into a text box cause a query to run and populate a list box so that if a caller has been entered before, the user can click on an existing name. So for example, if the first keystroke is A , then the list box will be populated with all names beginning with A. If the next keystroke is l to give Al then the list box will be repopulated with only names beginning with Al (e.g. Alice, Aliso...

Automatically fill down formulas when adding a record
I have a data entry sheet which receives one record per row. Some of th columns contain calculated fields and will be hidden. Is there a way t have the formulas automatically fill down when new records are added Thank you -- Message posted from http://www.ExcelForum.com One way is to use Excels built-in dataform. data>form -- John johnf 202 at hotmail dot com "steveski >" <<steveski.11dx8c@excelforum-nospam.com> wrote in message news:steveski.11dx8c@excelforum-nospam.com... | I have a data entry sheet which receives one record per row. Some of the | columns con...

rule size limit for mailboxes
Is there a way to increase the 32 KB size limit ? Thanks in advance ! Hans No it is inherent to the underlying rpc protocol. Nue "Hans Maris" <HansMaris@discussions.microsoft.com> wrote in message news:77A25DF8-50C8-4CB9-A2C8-F772243CD8EA@microsoft.com... > Is there a way to increase the 32 KB size limit ? > > Thanks in advance ! > Hans You might want to look at this. Let me know if you have any more questions. http://www.msexchange.org/tutorials/Set-Size-Limits-Messages.html On Thu, 18 May 2006 05:14:02 -0700, Hans Maris <HansMaris@discussions.micros...

New ti xml database
I need to use a file that is in xml format in vb.net. I'm new here. I have MSSql 2000 and vb.net 2003. I have accessed SQL files before. What is best. 1. Access the XML file directly? 2. Somehow import it into MSSQL 2000 3. Is there a better way. The file is publically available via internet. It must be updated periodically and accessed. Any Good advice will be greatly appreciated. jwc *** Sent via Developersdex http://www.developersdex.com *** Jim Bob wrote: > I need to use a file that is in xml format in vb.net. > > I'm new here. I have MSSql 2000 and...

.NET 2.0 XML Validation
I am wondering if I am alone with this problem. Using VS 2005, I must validate an XML file via a Schema and it works well. When I get the schema exception and check the LineNumber and LinePosition properties, they are always set to 0 <=== Here are 2 ways I used to validate the XML always getting LineNumber and LinePosition = 0. Any hints would be greatly appreciated. ================================================================== XmlReaderSettings settings = new XmlReaderSettings(); settings.IgnoreWhitespace = false; settings.Schemas.A...

View one record at a time in a report.
I am new to access. I created a report from a table using the wizard. When I view the report, I only want to see one given record at a time - not all records. How do I do this? Thank you Ron The easiest way is to start from a form, where you bring up the record you want to see. Then put a command button on the form, to open the report to just that one record. Here's the code you need for the command button: Print the record in the form at: http://allenbrowne.com/casu-15.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne...

Can't edit data in query
Hi everyone. Using A02 on XP. This may be a dumb question but I have a UNION ALL SELECT SQL query showing me all records from 3 tables [tDistributions], [tDistributionsArchive] and [tDistributionsArchiveOld]. Why can I not edit any fields? I receive an Excel file with uncashed check numbers, date mailed and payable to. I have to search through all distribution records to identify which distribution record's check did not cash yet so we can pursue payment. Because the tables are old and inherited, my unique identifier is only unique in 2 of the tables so there is a bit of sea...

Display Query Results on top of a Form
I have a query which runs from a control button on a form. When the query runs, the results are displayed behind the form. The user then has to minimize the form in order to see the query results. How do you set the query results to display on top of the form? You could put the following line in the code for the On Click event of the button being pushed: DoCmd.Minimize That will minimize the form and the query should be on top. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Kristin M" wrote: &...

check double records in database
Hey, I'm looking for a code to know if a row with primarty key ticker is already in my table DDM. I use the following code but there seem som problems. It says the variable TextTicker.Text is not defined. Can someone help me out? Thx s_Path = ActiveWorkbook.Path s_Path = s_Path & "\Thesis.mdb" Set DB = DAO.OpenDatabase(s_Path) s_SQL = "SELECT Ticker & FROM DDM " _ & "WHERE Ticker = ' & Me.TextTicker & " ';" If s_SQL = TextTicker.Text Then MsgBox "Found" Else MsgBox "Not found" GoT...

How to change left bottom note that tell number records found?
Hi, At the left bottom corner of Exel 2003 spreadsheet, a display note 'Ready' remains as it is, though I have filtered the items using the filter command, the note usually displays number of records found. How to return back the display note 'number of records found' in Excel 2003 spreadsheet? -- Thank you, Cpviv If you have any formulas that read these filtered records, you will typically not be able to view the # of records found. If you go to Tool - Options - Calculation and change to Manual, you would be able to view # of records. Beware, as this will not au...

displaying a table on a form so that we can insert a new record at any place rather that at the end
how can i display the table on a form so that the user is able to insert a new record not only in the last, but also in any place of his desire. he must be able to insert a new record even before the first record. how can i accomplish this? can any body help me out please........ hi Ravindar, ravindar thati wrote: > how can i display the table on a form so that the user is able to > insert a new record not only in the last, but also in any place of his > desire. he must be able to insert a new record even before the first > record. Tables in Access (in a database) are a differen...

Formsand queries
Through a query that uses grouping, I am gathering info from a City table. The table has two fields, the key field and the field City name. Sometimes, the query outputs two or more records. I would like to show the output of the query in a form, not as three separate fields, but as one field, separated with commas. Sample of query output: 534107106 Amsterdam 534107106 Muiden 534107106 Abcoude Sample of output in form: Amsterdam, Muiden, Abcoude What VBA code do I need to do in the form (and which event) to reach this result? Ron RoBo wrote: >Through a query that uses grouping, I a...

Excel 2007 Macro Recorder not recording shapes
Hello, I am trying to figure out why my Excel macro recorder is not recording creation of any shapes or changes to existing shapes. I started the macro, and drew a rectangle and stopped it. This is all the code I get. I like to see the syntax for shapes and I am unable to see maybe there is some problem with macro recorder. Please help! Thanks! Sub Macro1() ' ' Macro3 Macro ' ' ActiveWindow.SmallScroll Down:=25 End Sub Regards, Jaan On Feb 14, 2:35=A0pm, jaanbazz <jaanb...@domain-xyz.in> wrote: > Hello, > I am trying to figure out why...

Append Query Problem
Hello, I have a query that calculates a bank balance. I want this query to append the current days date and the bank balance to a table. I have a macro setup to autorun the query when my database opens, but can't get it to work correctly. Details: Table where I want the records added: BankingBalanceRecord Query that will run: BankingRecordBalance -This query is setup to pull today's date and the balance. It is funtioning properly. The main problem I am having is that if the primary record (Today's date) already exists, it gives me an er...

Count 350 SS numbers, exclude duplicates
I need to be able to count information based on approximately 350 social security numbers, exlcuding duplicates. Any ideas on the formula? Thanks so much, Marsha Hi Marsha, One way =SUMPRODUCT((A1:A200<>"")/COUNTIF(A1:A200,A1:A200&"")) -- HTH RP (remove nothere from the email address if mailing direct) "Marsha" <Marsha@discussions.microsoft.com> wrote in message news:E362D9A0-8A06-43E4-81DD-0B2F28C6C18E@microsoft.com... > I need to be able to count information based on approximately 350 social > security numbers, exlcuding duplic...

mulltiple column "not in" query
Is it possible to perform a multiple column "not in" query in access? I created the query select * from climktvaluefromgroups c where (c.group, c.invdate, c.marketvalue, c.clicode) not in (select t.group, t.invdate, t.marketvalue, t.clicode from tempclimkt t) and I got the error: You have written a subquery that can return more than one field without using the exists reserved word in the main query's from clause. Is this not possible? You cannot do what you are attempting. You MIGHT be able to use concatenation like this: select * from climktvaluefromgroup...

DataSet.ReadXMLSchema limitations?!
Hi I have a complex VALID schema, and I try to load into a dataset, but I get an Error: unidentified data type, but the type exist !!, and the xsd is valid (I tested against XML) As far I read from this forum, the dataset have some limitation reading complex schema, but there is some oficial information from microsoft about what kind of schema cause this... Thx ...

Record Deleted Error Message
Whenever I try to run a particular DB, I get an error message stating that the record has been deleted. Can anyone tell me what Access is looking for if nothing has been deleted? Thanks If you get this message just by opening a table, it probably indicates a corruption. Try a compact/repair (Database Utilities on the Tools menu.) If that doesn't fix it, see: http://allenbrowne.com/ser-47.html#RowsShowDeleted -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot or...

Exmerge & PST Limitations?
I'm looking to use Exmerge in a number of different ways on the Firm's Exchange 5.5 environment. However, I have a number of questions with regards to the limitations of Exmerge. As Exmerge uses PSTs to store emails, I imagine it is restricted by the 2GB limit of non unicode PSTs? That is correct. Nue "Pendragon" <Pendragon@pendragon.com> wrote in message news:440323b6$0$26028$ed9e5944@readers.news.pipex.net... > > I'm looking to use Exmerge in a number of different ways on the Firm's > Exchange 5.5 environment. However, I have a number of questio...

Using Form to update multiple records?
I'm creating an employee DB in Access 2007 and need to be able to track time submissions. I would like to be able to enter hours for each day (M-F), and the hours need to be categorized (vacation time vs. billable time). Ideally I would also like to generate a report from this information that will tell me the total hours worked in a given week (as well as a breakdown of how many hours were vacation, billable, etc). Initially, I tried creating a table that has this information, but I wasn't able to include a separate field for the day of the week, without having to manually ente...

my query returns null I need to do math on the returned value
I am working on yield, I have 2 qureys one for good and the other to sort the bad in to catagorys. I want to add the total good and total bad for total tested. Sometimes the results are null. When I add the two fields I get blank when I should get units tested. Using access 2007 Thanks in advance. I use the IIF statement, eg IIF([good]>0, [good], 0) which means that if [good] is >0 then use the value of [good], else use the value 0. If [good] can be negative or positive, then use: IIF([good]>0 or [good]<0, [good], 0) Do this for both good and bad. "rich L" wrote: ...

edit statement .XML
We were able to remove "Minimum Payment" but are unable to remove the amount part ($0.00). How can we do this? We would also like to do the same for "Credit Limit" and "Credit Available" along with the corresponding amounts. We would also like to remove the cash register logo that is printed alongside our name and address on the statement. Thanks in advance for your assistance. Matt ...

need help with query or report
I am needing to print out an inventory list that is in a box. I keep track of paperwork by using numbers from various sources (not able to change the sources) but each source has a number given to it. I keep each source serapate from each other in didferent boxes. I need to print out an inventory list that is in each box. I am just beginning using Access 2007. I have all 4 tables with data from each source ie. scanned forms, audit files and need to print out what is in each box. >> I am needing to print out an inventory list that is in a box. You need to use a term that corre...