Hi, I'm fairly new to developing apps for Sharepoint so please go easy! I'm having a problem joining two lists using CAML in Javascript. What I'm trying to do may not even be possible but from the research I've done so far I believe it should be, but I'm obviously going wrong somewhere. There are two lists in my app that are joined by a lookup column. The parent list is named TransactionHeader and the child list is named TransactionItems. For every item in the TransactionHeader list there will be one or more related items in the TransactionItems list. What I'm trying to do is retrieve items from the TransactionItems list but join to the TransactionHeader list so that I can also retrieve related data. The CAML I'm using is as follows:

var query;
query += "<View>";
query += " <ViewFields>";
query += " <FieldRef Name='ItemStatus' />";
query += " <FieldRef Name='ItemDate' />";
query += " <FieldRef Name='myReviewedBy' />";
query += " </ViewFields>";
query += " <ProjectedFields>";
query += " <Field Name='myReviewedBy' Type='Lookup' List='myTransactionHeaders' ShowField='ReviewedBy' />";
query += " </ProjectedFields>";
query += " <Joins>";
query += " <Join Type='Left' ListAlias='myTransactionHeaders'>";
query += " <Eq>";
query += " <FieldRef Name='TransactionHeader' RefType='Id' />";
query += " <FieldRef List='myTransactionHeaders' Name='ID'>";
query += " </Eq>";
query += " </Join>";
query += " </Joins>";
query += "</View>";

This CAML always results in a 'Cannot complete this action' message being displayed. A little more information in case it's important; after building the query I'm loading the TransactionItems list like so:

var ctx = new SP.ClientContext.get_current();
var camlQuery = new SP.CamlQuery();
camlQuery.set_viewXml(myCaml);
var list = ctx.get_web().get_lists().getB

I read somewhere else that this error could be caused by using reserved words in your query. Try adding square brackets around your table and field names: e.g. SELECT DISTINCT [Position] FROM [applicant] ORDER BY [position]

You're right actually, I probably don't need this feature. I have implemented server side validation which will ensure that invalid data doesn't get saved, so I suppose the post back is only a minor inconvenience to the user. There will still be the inline validation so the likelihood is that the user will correct their errors before submitting anyway (or maybe not!). Thanks a lot :) Jay

WOW! You've either put a lot of thought into this answer or you've tackled this problem before! Either way thanks for the response, I will try this later today and let you know how it goes. Thanks again Jay

You have a circular reference somewhere; does the class 'ClassName' from imported DLL have a reference to class 'ABC'?

Hi, I have a registration form which consists of, amongst others, an email field. When the user enters their email address, the onChange event is fired and a server side page method is called which checks the email address and returns a boolean value. This works fine when invoked from the onChange event of the textbox. The problem occurs when the submit button is clicked; the submit button invokes the Javascript validation function and posts back if the form is valid, however, because the email validation involves an AJAX server side call, the function isn't waiting for the response and so causes the form to be submitted regardless of the email validation result. I hope I have explained this adequately but if not I have included a cut down version of my page:

var pageIsValid;
var emailAddressMessage;

function ValidateForm() {

    pageIsValid = true;

        PageMethods.EmailAddressIsValid(document.getElementById('<%= txtNewMemberEmailAddress.ClientID %>').value, ValidateEmailAddressSuccess, ValidateEmailAddressFailed);

    // other validation calls have been left out

    if (pageIsValid == false) {
                alert('Some information is either missing or invalid.  Hover your mouse over the red crosses for more information');
        }

        return pageIsValid;
}


    function ValidateEmailAddressSuccess(result) {
        if (result == false) {
            document.getElementById('<%= imgNewMemberEmailAddress.ClientID %>').src = "/Images/cross.png";

            emailAddressMessage = 'The email address you have entered is already in use';

            pageIsValid = false;
        }
        else {
            document.getElementById('<%= imgNewMemberEmailAddress.ClientID %>').src = "/Images/tick.png";

            emailAddressMessage = 'This field is valid';
        }
    }
    function ValidateEmailAddressFailed(result) {
        document.getElementById('<%= imgNewMemberEmailAddress.ClientID %>').src = "/Images/cross.png";

        alert(result.get\_message());
    }

If anyone could suggest a better way of doing this it would be appreciated. Thanks Jay

Ah! I didn't realise that you could specify what columns fired the trigger. This is the current trigger definition:

CREATE TRIGGER [dbo].[trgProductCategoriesUPDATE]
ON [dbo].[tblProductCategories]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

UPDATE	tblProductCategories
SET	\[DateModified\] = GetDate(),
	\[AwaitingSync\] = 1
WHERE	\[ID\] = (SELECT \[ID\] FROM INSERTED)

END

Which fires when any column is updated. Are you saying that it should look something like this:

CREATE TRIGGER [dbo].[trgProductCategoriesUPDATE]
ON [dbo].[tblProductCategories]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    IF UPDATE(CategoryName)
    BEGIN
        UPDATE  tblProductCategories
        SET     \[DateModified\] = GetDate(),
	        \[AwaitingSync\] = 1
        WHERE	\[ID\] = (SELECT \[ID\] FROM INSERTED)
    END

END

Thanks :)

Hi, Scenario: I have a database which is going to be replicated via a set of web services. The web services will select all records where [AwaitingSync] = true. I have cerated two triggers for each table which sets [AwaitingSync] to true when a record is inserted or updated. The web service then sets [AwaitingSync] to false for all records. Problem: Due to a combination of my inexperience and lack of thought there is a slight problem(you'ver probably already noticed what the problem is!). After synching, when the web service sets [AwaitingSync] to false the update trigger fires and sets it back to true! :doh: My question is this then; what can I do about this? Can I add a conditional statement to the trigger so that it only fires if a particular column is being updated(or not being updated in this case). Or maybe the trigger be temporarily disabled while the [AwaitingSync] column is being updated. Or are there any other solutions to this? Thanks for your time. EDIT: Think I've found a solution but I would still appreciate your views if there is a better way of doing this. I have modified the trigger to check the value of [AwaitingSync] before updating.

IF(SELECT [AwaitingSync] FROM DELETED) = 0
BEGIN
UPDATE tblProductCategories
SET [DateModified] = GetDate(),
[AwaitingSync] = 1
WHERE [ID] = (SELECT [ID] FROM INSERTED)
END

modified on Monday, October 25, 2010 11:21 AM

OK, thanks a lot. Sounds like a good idea, will give it a go tomorrow. Thanks again for your help :)

Hi, the line which reads

doc.Close(false)

should that be

word_doc.Close(false)

And also, you have an object named word_server, where is that declared?

Hi, Throughout my application I have several ComboBoxes which are data bound to a collection of objects (uaually a generic list). Because the user needs the option to not select an item in the Combobox I need an empty item at the start which says something like "Please Select". The way I am doing this currently is to add an extra object to the start of my collection which gives me what I want. However, because a lot of these Comboboxes are bound to a collection of objects that are built when the application starts and remains in memory for the duration of the users' session, it means that my collections have an extra object in them which is causing problems elsewhere in the application. Is there any other way of inserting an item into the start of a combobox that is bound to a collection of objects? I hope this makes sense, if not then please let me know and I will try to clarify. Thanks for your time. Jay

yes, although I don't recall what the solution was! I will look when I get to work tomorrow.

Sorry for the delayed reply but I was tavelling home from work. I see that you have some good advice now from other people, so I hope your project goes well. And forget about the Application.MakeFolderOrganizer() method, it's not going to help you in this case ;)

Taku Kawamoto wrote:

Please give me a few ideas on which statement I need to use.

Have you tried Application.MakeFolderOrganizer() ?! :D But seriously, your question is a little vague. Can you supply in more detail what your are trying to do and have you tried anything yet? But going on your question try looking the File class; it provides lots of functionality for creating, moving, copy files and folders etc.

Another lesson learned :D Thanks

Thanks both of you but I have resolved it. I was using:

Dim outFile As IO.StreamWriter = My.Computer.FileSystem.OpenTextFileWriter(csvFile, False)

which was creating the file as UFT-8 which seemd to insert the BOM. I have changed it to:

Dim outFile As IO.StreamWriter = My.Computer.FileSystem.OpenTextFileWriter(csvFile, False, System.Text.Encoding.ASCII)

which doesn't insert the BOM. Thanks for your time.

Hi, I have developed a rountine that builds a string using the StringBuilder class and then creates a CSV file from the string. The CSV file is then used to import data into another application. However, after creating the CSV file there is BOM at the start of the file which is preventing me from importing into the 3rd party application. My question is how can I create the CSV file without the BOM? Thanks for your time.

OK Gideon that's great, thanks for your time and advice :)

Hi, I have developed a class library to be used in a Windows project as well as a web project. Because I am developing plugins for a 3rd party CMS, the web project requires me to add some references to the class library so that I can comunicate with the CMS from my plugins. The back office Windows application, however, doesn't have the referenced CMS files available so the application fails. Do I need to copy all the referenced files to each machine where I want to use the class library, even though the Windows app doesn't require them (none of the class library procedures called from the Windows app uses the referenced CMS files). Or is there some way I can exclude them when compiling the class library for the Windows App? Thanks for your time.

Are you able to post the error?