Field changes from stored to unstored!

[cmartin]

HELP!

I have a calc field

PrintKey(number) = Case(Location = "A" and IsEmpty(PrintDate),ForiegnKey,"")

I want to use this in a relationship so it has to be stored and indexed. FM allows me to set it as such, but then when I leave DEFINE FIELDS window and come back to it, it has been changed to UNSTORED and I cannot use it on the right side of my relationship. Fields Location and PrintDate are both local to the file. The ForiegnKey is a lookup, but that shouldn't matter should it? PLUS, FM allows it while I'm defining it.

Why is this happening?

Thanks-

Courtney

[Ugo DI LUCA]

Your calc looks good for me. Are you sure it is really unstored. Did you unchecked the automatic index in the option and set it to be stored ?

[Lee Smith]

Hi Courtney,

By chance is the date field being populated by the Status(CurrentDate) Function?

[cmartin]

Ugo-

The indexing and storing is done correctly. After I leave the specify calculation window and am back in Define Fields it shows the field as:

HS6PrintKey Calc Indexed,=Case(etc.)

However, if I exit Define Fields and then come back to it, it then shows

HS6PrintKey Calc Unstored,=Case(etc.)

Lee-

PrintDate field will eventually be populated by a script using Status(CurrentDate) but I have not even gotten that far yet. So currently for all records PrintDate is empty. Why do you ask? Will this lead to a problem?

Have I come upon a bug?

Thanks-

"Losing my mind" Courtney

[Ugo DI LUCA]

Well, I tried to reproduce your calc on my own files and it works fine. There must be something wrong. Could you make a copy of your file with some records on it and post it back.

If not, I will wait for one of the genious guys here and see what answers they can give.

[cmartin]

I'm going to post the file as you suggested UGO. Until then, thought I would let you know the result of some troubleshooting. The problem seems to be related to the ForiegnKey part (which is a lookup field). I changed the calc to Case(Location="A" and IsEmpty(PrintDate), "Y", "N") and it worked fine. Odd, but true. Geniuses, are you out there?

Thanks-

C

[Lee Smith]

Courtney,

Status(CurrentDate) can not be indexed, in fact the right side has to be able to be index. How is the ForiegnKey derived at?

It might be easier for you post them here as an attachment and tell us what you want to see in what files.

Lee

[cmartin]

I hope I did this right. Please, please, please excuse the nastiness of the layouts and the organization. This is NOT my design. The field of interest is HS6PrintKey in the dsmb file, which can be seen on the last layout. Thanks!

-Courtney

DSMB.zip

[cmartin]

Lee-

PrintDate will be set using SetField(PrintDate) = Status(CurrentDate) from within a script. It is not a calc field and, therefore, should have no problem being indexed. The lookup field that is causing the problem is called INDDrugInfo.key. When a user chooses a StudyINDDrug by name from a pull-down, this populates the key field. You can see this in action on the download if you go to the last layout. I really thought look-up fields were okay to use in calcs, though.

Courtney

PS I am very embarrassed at having to post a file that is as icky as this one, but let me reiterate: this was an inherited db and my boss does not care to have it fixed. WAAAH!

[Lee Smith]

Need a password for access to the Drug Master.FP3 file.

Also, what is the information you need to pull out of Drug Master.FP3 File?

BTW, I'm not spotting HS6PrintKey on the last layout, but it is on the next to last layout. Only one record has a Key and it's 1, correct?

[cmartin]

Oops! Okay let's try this again. I removed the password from Drug Master and all the superfluous layouts from DSMB Clone. There is only one record that currently has the HS6PrintKey set and it does equal 1.

DSMB.zip

[Lee Smith]

Okay, what information do you want over in the DSMB database from Drug Master?

)

[Lee Smith]

Also, I do not see a field in Drug Master called ForiegnKey.

So, what is the related file key?

Lee

[cmartin]

I want the HS6PrintKey to be set to the RecordID (from DrugMaster) of the selected Study Drug if the Location=Non-UCLA and HS6PrintDate is empty.

Make sense?

Thanks for your patience-

Courtney

[Lee Smith]

I must be missing something. The relationship setup too easy. Here are the files, please let me know if this correct.

Lee

download 2.php Folder.zip

[cmartin]

Lee-

I'm not sure what I'm supposed to be checking out. The files you attached are the same as the ones I uploaded, no? Remember, the original problem here is why HS6PrintKey allows itself to be set up as STORED and INDEXED (which I need it to be for relationship purposes), but then mysteriously switches to UNSTORED if you exit and then return to DEFINE FIELDS.

I know I could probably use the work around for making non-indexable fields indexable, but I am confused as to 1) Why the field cannot be indexed, as it is not based on a related or global field (only a lookup, which, as far as I know, should not be a problem) and 2) Why, if for some reason I cannot identify it cannot be indexed, does FM allow me to set it to STORED and INDEXED without the tell-tale error message and show it as INDEXED and only switches to UNSTORED after exiting the define fields window?

Phew. Hope that makes sense.

Courtney

[cmartin]

Okay, I just looked again at your attachment Lee and it seems that in this copy, HS6PrintKey remains indexed. Did you change anything? Because in my copy, this is not the case. Now, I'm thinking I must have a corrupt file.

[cmartin]

I also just checked the file that I uploaded and the problem does not happen there. The file that I uploaded is a clone with many fields and layout deleted. Definitely pointing toward corruption. %$&@#!

Thanks for all the help, Lee. No wonder we having so much trouble communicating. You were seeing a different outcome then I was.

-Courtney

[Lee Smith]

Keep at it, and if I can help some more let me know.

[Ugo DI LUCA]

Hi Courtney,

This was a corrupted file. I had this problem twice and it's very confusing as everything seems fine. Glad you found out. I'll open a new thread on this as I still doesn't understand why a file can get corrupted from ane day to another.

[cmartin]

Thanks, Ugo. Yes, a new thread is a great idea because I have now spent the better part of a day going in circles with this and I now want to scream. AAAAAAAAAAAAARRRRRRGGGGGGGHHHHHHHH.

Okay, I feel better now.

[Pupiweb]

Hello Courtney

If you have a look up field that uses an unstored field for triggering the lookup the field itself is indexable but calc fields that include the field in their formula will exhibit the behaviour you mention: they can be set to stored but will silently revert to unstored

[cmartin]

The lookup field is indexed. The trigger for the lookup is also stored and indexed. Interesting that you have experienced this before, though. Seems like a bug in FM that a field should silently revert, regardless of the cause.

-c