Newbie Questions

Re: Difference of nz(@ID) and max(@ID)

Jimmy — Mon, 23 Aug 2010 17:09:45 GMT

If you're using rules to assign the ID value, you can use separate rules with different conditions. That's just as good as an if/then statement.

Re: Difference of nz(@ID) and max(@ID)

mpeters — Mon, 23 Aug 2010 15:17:13 GMT

Jimmy,

I have a retrieval data connection for each forms library in SharePoint. The secondary data source has a repeating group with just the ID field. So everytime I submit a form to this library a new ID in a new row is created??? If that is the case, then I guess it would be retrieving multiple rows. I thought that there was just the one field called ID and I was always getting that one.

So, it looks like I absolutely would need to use MAX (@ID) then. Too bad that MAX couldn't also do the check, so that you wouldn't get the dreaded NaN error when there are no forms in the library. I was told that I can't use code to perform the checks for NaN, so I'm sort of stuck, unless I put a form or folder in every library. I have quite a few libraries, so I am already using the condition statement in the submit rules to determine which set of actions that I want to use for the submit. Too bad there aren't some standard 'if / then' statements in the actions available. Any thoughts?

Re: Difference of nz(@ID) and max(@ID)

Jimmy — Fri, 20 Aug 2010 17:05:55 GMT

If your query returns multiple rows, then max(@ID) will select the highest ID value. nz(@ID) would return the value of ID in the first row of the query results.

Does your SharePoint list query retrieve several rows of results, or just one?

Re: Difference of nz(@ID) and max(@ID)

mpeters — Fri, 20 Aug 2010 12:52:23 GMT

Thanks for the reply Hilary!

When you are using these functions specifically with the ID field for auto-numbering the forms, shouldn't the result using NZ or MAX be the same? When I setup my data connections for my form libraries and I select ID from the SharePoint list, I'm thinking it is just one field, so NZ or MAX should have the same result right? My tests do not support that conclusion, though.

Re: Difference of nz(@ID) and max(@ID)

Hilary Stoupa — Fri, 20 Aug 2010 00:40:14 GMT

Hi:

From Office Online, nz "Returns the value of the specified field. If the field is blank, it returns zero (0)", whereas max "Returns the largest number in the argument".

Practically, that means that nz is only looking at a single field -- so if your field is, for example, in a repeating data group, it will only evaluate the first instance of the field in that group. Max, on the other hand, evaluates all instances of a field. So, if your field is in a repeating group, max looks at all instances of the field and returns the max. Does that make sense?

Difference of nz(@ID) and max(@ID)

mpeters — Thu, 19 Aug 2010 20:11:26 GMT

What exactly is the difference in what is returned? I set up a field that displayed both. After initially getting zero for NZ when the folder was emply, I didn't see a difference for a while. Then, after saving a form and opening it up and submitting it, there was a difference. After that the nz result didn't change, even when I set autonumber = nz(@ID), then display autonumber + 1.