Problem updating userdata

Posted on 24/03/2010 22:46:36

I have created a form, that automatically inserts user-info, such as the ExtranetID into a table. Also the name of the user and the current date are inserted after you login and complete the form. The user then only has to choose a number of options from a dropdown-list, in this case "Tilmeldt" or "Afmeldt" and press submit.

However, I am experiencing a problem:
If the same user completes the form, for example twice, the user will also appear twice in the datalist-view.

You can see the view and what I mean here:
http://lbr.net.dynamicweb.dk/Default.aspx?ID=345

XML here:
http://lbr.net.dynamicweb.dk/files/Templates/DataManagement/Publishings/Custom/Tilmelding_TestData.xml

What I want is, that only the newest created entry per user gets shown.

When using html forms and lists, you would be able to do this by checking of checkboks:
"Allow the form to update the existing data"

This however, does not work on my form, for some reason.

I have therefore tried creating a custom sql-view in the datalist-module, trying to group the rows and also choosing the max id of the tableId. This however only outputs a few of the columns and not all of them. I want the other columns shown as well, or else there is no point in trying what I am trying.

I have also tried looking at The Muenchian Method, but without succeeding to exploit the method properly, obviously.

To make it even more difficult, it is important that it is not necessary to manually write the name of each of the columns in the xslt-template, because I will need to add new columns, without having to edit my list-view or xslt-template. It needs to be dynamic in other words :)

Replies

Posted on 25/03/2010 10:59:19

Hello Britz

What you need is the correct SQL for selecting and grouping your output before the XSLT rendering.

Try something like this:

SELECT Tilmeldingsformular_Id, Tilmeldingsformular_Navn, MAX(TilmeldingsformularID) as recordid

FROM [Tilmeldingsformular]

WHERE [Tilmeldingsformular_Aktivstatus] = 1

GROUP BY [Tilmeldingsformular_Id], [Tilmeldingsformular_Navn]

ORDER BY recordid DESC

It's always easier to do in the SQL than in XSLT where, as you say yourself, you use the Muenchian Method.

// Sebastian

Posted on 25/03/2010 11:26:37

Hi Seb,

Thank you for your effort, but I am afraid this method is much like the one I already have tried. The problem with this is that it wont allow me to output the other collumns along with this output. The version I tested was:

SELECT MAX(TilmeldingsformularID), Tilmeldingsformular_Navn
FROM Tilmeldingsformular GROUP BY Tilmeldingsformular_Navn

Which is very similar to yours. If I add the other columns it would look like this:

SELECT     Tilmeldingsformular_Id, Tilmeldingsformular_Navn, MAX(TilmeldingsformularID) AS recordid, Tilmeldingsformular_Dato, Tilmeldingsformular_T060410, Tilmeldingsformular_T080410
FROM         Tilmeldingsformular
WHERE     (Tilmeldingsformular_Aktivstatus = 1)
GROUP BY Tilmeldingsformular_Id, Tilmeldingsformular_Navn
ORDER BY recordid DESC

However, this gives me the following error:
Tilmeldingsformular.Tilmeldingsformular_Dato is invalid in the select list because it is not contained in either an aggregate function or the GROUP By clause.

Of course, I could always ask people not to fill out the form, more than once :)

You must be logged in to post in the forum

Developer forum

Problem updating userdata

Replies