I'm trying to get null stack fields to appear as empty strings on a form, and the recommended Sys_Mgr command and a change to the edasprof are having no effect. Has anyone gotten a NODATA setting to stick in Maintain?

J.

This message has been edited. Last edited by: John_Edwards,

.


If I may paraphrase King Henry,

"Will no one rid me of this troublesome bug???"


.

John
This problem has been identified and I believe there are projects to fix it. I am not sure if they are available or will just be in the next release.

If the problem is ONLY on a field or two, what I have done in the past is redefine the field with MISSING to an alpha field without it. So, I can control the display. Then, before the update I compute the value from the computed field into the database field.

If you cannot use the work-around, and cannot wait for the next release, open a case with Customer Support and they can see if a patch is available.

Mark

I just had this same issue. I ended up actually checking to see if stack fields corresponding to my edit boxes have the value of MISSING and then replacing MISSING with "" if they do. If I need to write those stack fields back to the database (adding or updating) I checked to see if they're "" and if they are assign them the value of MISSING. That way NULLs are kept on the database tables but my forms have nice empty edit boxes rather than ones with a single period in them. It's a little tedious but I prefer the empty edit boxes so it was worth the little bit of extra code.

A year later and I am still looking for an answer to this question. Has anybody figured out a way to make nulls actually honor their assigned character?

SYS_MGR.FOCSET("NODATA","") does not work for me, nor does setting the NODATA parameter in the profile.

7.6.5 on NT

J.

John
The oldest release I have is 7.66. When I place the field TEST on the form and run this code, the field is displayed with a blank, not a dot. I don't know if the repro is different for database fields, but give this a try. If you see a dot instead of a space then upgrading should solve the problem.

Mark

MAINTAIN

$$Declarations

Case Top
SYS_MGR.FOCSET("NODATA", "");
COMPUTE TEST/A5 MISSING ON;
Winform Show Form1;
EndCase

END

.

It must be a version thing. I'm compiling on a 7.6.5 server and I don't get the character, except for date fields. Date fields honor it. But they roll everything to upper-case which is strange. Setting NODATA to "j" results in "J" being displayed in date fields.

If you can make it work for text fields in 7.6.6 then it is likely the first version to fully honor the command. Now I just need to convince my customer to upgrade.

Thank you!

J.

.


Ok, upgraded to 7.6.6 and the null fields coming back from the databases do indeed honor the nodata sys_mgr command.

Fields returned from substrings do not however, so I needed to mitigate by adding computes that look for dots in the fields. An example --

compute StkApplicantEdit(1).SSN_Formatted = substr(StkApplicantEdit(1).SSN, 1, 3);

When SSN is returned from the database as an empty string, this line results in a '.' being entered into SSN_Formatted. I needed to test for that dot and replace afterwards, like this --

if StkApplicantEdit(1).SSN_Formatted EQ '.' then compute StkApplicantEdit(1).SSN_Formatted = '';


Note that comparing SSN_Formatted to MISSING in the above line of code did not work, you need to look for the value '.' A simple enough task once you know what you're looking for.

Thanks for all the help folks!

J.



.

Adding to this thread... I'm running into a problem with using SYS_MGR.FOCSET("NODATA","") in Maintain 7.6.4.

Setting NODATA to a value seems to be honored by date-time (HYYMDI) fields when they're null but not by non-date fields. That's fine. I can workaround the problem for the non-date fields.

I have some HYYMDI data source stack columns (smalldatetime columns on a SQL Server 2000 table) bound to edit boxes on a form. Without setting NODATA I see the usual single period in the edit boxes. Setting NODATA to "" leaves the edit boxes empty, which is what I want. The problems start when I try to update or add a record to the data source with NODATA set.

When I try to update or add a record I get this error:

(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
: Microsoft OLE DB Provider for SQL Server: [22018] Invalid character valu
: e for cast specification.
(FOC1740) EXECUTE ERROR : WFSERVER

In edaprint.log I'm seeing these messages:

03/22/2010 15:57:23 Pid=00002724 There is a conversion error from external to internal format: A0 to HYYMDI

As soon as I comment out the SYS_MGR.FOCSET("NODATA","") the updates and adds work fine and the conversion error messages in edaprint.log are no longer there.

So because I've got datetime fields bound to an edit box on my form it seems there's some kind of conversion from A0 to HYYMDI going on but only when NODATA is set to some value.

All fields except those in the primary key are nullable on the table so those fields all have MISSING=ON in the table's master.

For the non-date fields I just add some A0 computed stack columns, copy the corresponding data source fields to them, and bind them to the edit boxes instead of using the real data source fields. That makes sure that any fields with null in them are displayed as nothing in the edit boxes.

I want to do the same for the datetime (HYYMDI) fields. Make the edit box blank if the field is null or have the actual datetime value (e.g. 2010/03/22 15:30). If I set NODATA to "" then it works but then I can't update or add records. If I don't set NODATA then adds/updates work but I'm back to seeing a dot in the edit boxes for the date-time fields. Argghh.

There's something going on with NODATA and that conversion error. Is NODATA actually changing the value in the field from NULL to something else, thereby causing the conversion error? What about the conversion message of A0 to HYYMDI? Where's this A0 coming from? Any ideas?

Thanks,
James

The SQL error you get indicates an incorrect value for a date/time field. In this case it is probably caused by the field having a value of 0.
I have had exactly the same problem as you face now. The only way I was able to solve this was by adding a checkbox on the screen to indicate that the field should have a null value.
The problem is not with maintain. It is also not with the server. The problem is with the way the browser returns an empty value to the calling process. In this case what happens is that a space (or blank or nothing) is in the field and since it is an number field, it gets translated to 0 - and that gives an error.
The A0 comes from the internal conversion that needs to be done to display a HYYMDI (binary) fiels in readable format ont he screen. Format A0 is a very convenient format, since it has no limit to the length and thus can contain very variable data.

Hope this helps a bit ...

I think that I generally use for any fields with MISSING attribute and NODATA blank:

if stack.datefield eq ' ' then stack.datefield = missing else stack.datefield = stack.datefield;

although I do know that in one instance I use a separate field for display, where I use:

if displayDateField eq ' ' then stack.datefield = missing else stack.datefield = displayDateField;

What I found cannot be used is

stack.datefield = if stack.datefield eq ' ' then missing else stack.datefield;

I believe this has been raised as an issue.

GamP, that makes perfect sense. I was thinking it must have had something to do with the displayed data actually being A0 because it's human readable data in an edit box on a form even though HYYMDI is binary. Thanks for clarifying how that works. The edaprint.log messages make sense now.

Alan, when you used a separate displayDateField, what format did you use for it? A0? How did you validate it so that someone either left it blank or entered a valid formatted date-time or date value like 2010/03/23 00:00 or 2010/03/23?

I think I tried doing the if field='' then field=missing test on the date fields but I think it didn't seem to work like it did for the non-date fields. But I'll try it again.

Thanks,
James

Just a followup. I've gotten the adds/updates to work by using an A16 computed stack column for the edit box and using HCNVRT and HINPUT to convert the values back and forth between HYYMDI and A16. So I'm able to keep setting NODATA to "" and still be able to get the date-time value from the edit box into the data source and vice versa. So the problem is [Solved].

James

James,
That has put me on track again - thanks! I somehow never even thought of doing the conversions myself and profit from that. Now my rather annoying checkbox can go!

I only use date formats for date fields, and never had a problem. Don't really see why you would use an alpha. At some points I know I have used a non-database field (temporary) field on a form, but always in date format. Dates I deal with are nullable and I also hate the '.', so I know this works.

For validation I use a js reg exp, which was posted somewhere here a while back, or use the calendar, particularly for date/time fields. Must admit I have changed the calendar a bit to make it more user friendly, and I believe this is on a NF list.

A tip for using the calendar is to set the field as RO with js when the page opens, so the user is forced to use the calendar and no validation is required. Not to hand at the moment is a tip from Mark to set the start and end points of the calendar which is very useful.

Just a quick note to say that in 7.7 you will no longer have to add JS and CSS files to make the calendar pop on your form. We have added a Calendar property to the form and to each field. It can be set on for every date field on your form and turned off when necessary.

We also have a new Grid control that is pure JavaScript. The Active X grid is still there, but now you will have the option. This object also lets you use HTML in the cells to easily build drop down lists and radio buttons, etc... We added StyleSheet and OnLoad JS options to the form properties too.

Mark

Sounds great, I can hardly wait to get some hands-on with 7.7.
I'll just have to sit and wait for 77 to come out.

Will this new Calendar also popup in the same browser window that the current form is in? Is there a way to make either the old JS calendar or this new calendar in 7.7.x popup in a new browser window on top of the current one that contains the form with the date fields? My forms have zero free space in them and the browser window is resized to 642 x 700. Don't ask... it was one of the more important requirements for the application.

I might do a little Googling on JS popup calendars but thought I'd ask here first. I'd like the user to be able to click or tab to a date field on the form and then the calendar would pop up in a new small browser window on top of the current window.

Thanks

Never mind my last post. The issue isn't that the calendar won't fit when it actually pops up. I see that it just overlays whatever's next to the little calendar icon. My problem is that I have no space for the little calendar icon at all. All my fields sit right next to one another. If I can just find a way to squeeze in that calendar icon I'll be good to go. So never mind my question about the calendar in the separate window. Thanks.

I can show you how to do that on the double-click trigger if you like. No additional space, though you'll need to train your users that it's there.

J.

I spoke too soon. Even if I can find space for the dynCalendar 16x16 icon there's still problems. If the icon ends up being too far to the right in the form then the popup calendar extends past the right border of the browser window and I get a horizontal scroll bar on the browser window, which is a big no no in this application. So I do need a popup calendar in a separate browser window after all.

John, are you referring to a popup calendar in a separate browser window?

Thanks,
James

Yes. I use something similar for selecting from cross-reference tables, but there's no reason you couldn't use it for an external calendar widget, or even the WF calendar widget attached to a field.

J.

That sounds good. I'd be interested.

Figured out how to get rid of the 16x16 calendar icon and assign the *_calendar.show() function call to the edit box itself on a double click. Now I can go back to a regular date field and use the calendar. I'm also going to work on making the popup calendar smaller by changing the CSS and JS where appropriate.