script problem for only two seats out of ten

[Greenman]

Greetings,

I am not sure where else to post this, so I'm posting on this subforum. It's dealing with fields, but also seems to be dealing with networking.

I have a ten seat system where a database works fine on all but two machines. I have uninstalled and reinstalled filemaker pro 9, and dumped all filemaker user preferences to no avail. All users are using FMP9.0v3.

I installed FMP9 Advanced on one of the problem machines. The script runs fine when you step through, but when you don't have debugging on, it still gives the same warning error.

I'm using an adapted templated filemaker solution "ClientTracker" from iSolutions that came straight out of the box with filemaker 7.0. Up until about two months ago, nobody was having problems. Then suddenly, we keep getting the above error any time we add a new project to the db.

This application runs smoothly on all other machines. It's just these two that are problematic. This is the warning dialog box I get:

"zk_P_projectID.t" is defined to contain unique values only. Allow this duplicate value?"

You have to revert, or it gives an existing id to the new project.

The script works solidly on 8 out of 10 seats.

Has anyone else had this problem, and does anyone have a solution?

[bcooney]

Really, I can only guess. "zk_P_projectID.t" has Unique Validation assigned to it. From its name, one can guess that it's the primary key, and it's a text field. What's scary is that it might be a calculated primary key. Given that, perhaps it includes the only thing that can be specific to a user--a global value.

So, the question is, is it the two workstations (key uses NIC address?) or these two user account logins (some global is set at startup)?

You'd have to talk to the developer if you can't see exactly how this field is defined.

[Greenman]

Yes, it is a text field, unique id.

It does not use part of the NIC.

It's just six random characters.

I know that's frowned upon, but it was working fine for quite a while, and now it's not.

Invariably, the characters generated either end up being the very first unique id (on one machine) or the very last unique id (on the other machine). All other machines apply the 'new project' script flawlessly.

All globals are set to "" in the open script to prevent problems. No globals are used to generate the project id.

[bcooney]

Calculated keys are your problem. Really, end of story.

[comment]

I wouldn't be so quick to pronounce a verdict. Unless you can explain why the same calculation/script exhibits different behavior on 2 out of 10 systems.

This, BTW is a sequel to:

http://fmforums.com/forum/showtopic.php?tid/194847/post/308480/#308480

[bcooney]

Is it 2 out of 10 accounts or workstations? Could be a key to understanding the behavior. But honestly, not going to waste my time on this.

Maybe he should adapt Ray's uID.

[comment]

Is it 2 out of 10 accounts or workstations?

I don't know. I haven't seen the exact script either. That's my point.

[Greenman]

Sorry, workstations, not seats.

Thanks for your patience.

This is the first time I've been asked to change an ID system mid stream. As it happens, id's were originally incremental numbers. However, about half the year into using the system another colleague changed to a different id system using date and random characters.

About a year later, they decided to shorten the ID even further using just random characters. That task fell to me. I've created id systems in LAMP that have used all sorts of crazy techniques. Not having used random numbers for id's before in filemaker, I hadn't realized it was going to be such a teaser.

To top it all off, I'm only budgeted one day a week to work on this, and spending this much time on one little puzzle is raising eyebrows, where I'm usually able to solve this kind of thing in my sleep. I'm stumped.

As I said before, it worked fine at first. Then it started mutating on two machines. No scripting is local. All of it is served from the host. That's what made me think there may be a flaw in the random function in Filemaker. One machine always starts with the oldest id as duplicate. The other machine starts with the most recent. If you attempt to create new id's these two machines long enough, the script starts working properly. So when I try to fix the script, I get just so far before it looks fixed. But it's not. The next day, the same problem occurs. I wonder if it has something to do with the way random is seeded. If there were a way to seed the random function, I suspect it might work better. If anyone has ideas on how to do that, I'd consider trying it.

I've done disk utilities to repair any problems on each Mac. One is an iMac, one is a desktop. Both are intel machines, so it's not necessarily hardware-specific.

But maybe I should start over...

I need a people-friendly ID system that is easy for humans to input - 8 characters or less - and will not throw the system out of whack. Each ID should be the same length and unique.

I also could use a goof-proof method for transferring from the old project ID's to the new project ID's. I'm sure this kind of thing happens all the time when you're merging disparate sets of data. Two banks account id systems, for instance. There must be a system.

If there is a quick reference for doing these things somewhere on the web, I would be indeed grateful.

[David Jondreau]

I need a people-friendly ID system that is easy for humans to input - 8 characters or less - and will not throw the system out of whack. Each ID should be the same length and unique.

I also could use a goof-proof method for transferring from the old project ID's to the new project ID's. I'm sure this kind of thing happens all the time when you're merging disparate sets of data. Two banks account id systems, for instance. There must be a system.

Why not simply use FM's built in auto enter serial? You can make it 8 digits, which is user friendly. Phone numbers are 7 or 10 digits long and we input those all the time (or we used to until mobiles).

[comment]

I need a people-friendly ID system that is easy for humans to input - 8 characters or less - and will not throw the system out of whack. Each ID should be the same length and unique.

I thought this was already solved in your previous thread.

As for switching the ID's, the basic method is this:

1. Show all records in the parent table, and populate a NEW primary key field;

2. Show all records in the child table, and populate a NEW foreign key field with the value of the parent's new primary key (using the existing relationship to fetch the value);

3. Switch the relationship to use the two new fields as matchfields.

So when I try to fix the script, I get just so far before it looks fixed. But it's not. The next day, the same problem occurs.

I don't think anything useful can be added to this until you post your exact script.

[Greenman]

The ID is a calculated field with this calculation:

/* generate six random characters and display them */

Let ( [ alpha = "23456789ABCDEFGHKLMNPQRSTUVWXYZ" ] ;

Middle ( alpha ; Ceiling ( Random * Length ( alpha ) ) ; 1) &

Middle ( alpha ; Ceiling ( Random * Length ( alpha ) ) ; 1) &

Middle ( alpha ; Ceiling ( Random * Length ( alpha ) ) ; 1) &

Middle ( alpha ; Ceiling ( Random * Length ( alpha ) ) ; 1) &

Middle ( alpha ; Ceiling ( Random * Length ( alpha ) ) ; 1) &

Middle ( alpha ; Ceiling ( Random * Length ( alpha ) ) ; 1)

)

/* Old Method for getting project ID

Let([ N = GetAsNumber( Get( CurrentTime ))];

If(Length ( N ) > 5; Left ( N ; 5 );

If(Length ( N ) < 5; N & Middle( Random ; 2; 5 - Length( N ));

N

)

)

& "-" & Middle( GetAsText( Random ); 2; 9)

) */

The old one worked because a time element was brought into the mix. The new one doesn't work because it always uses the same seed for random generation. The first two instances on my two machines is invariably N7B7N5, then 883VD2, then 5EH5QK. All a user has to do is log in, ask for a new project and these numbers pop up.

So I created a simple program with just one table and one field autocalcuated to use the random calculation from above. The first ID? N7B7N5. Second? 883VD2. You can guess that it continues.

I closed down FM, restarted my simple db and created a couple new records. Within the second record it started repeating - giving the same error that started this journey.

Regardless, FM Pro 9's "random" isn't really random. Why they haven't introduced a seed variable, is a heavy query indeed.

Random should mean random. There is likely a flaw either in Filemaker's randomizing function or a flaw in the Macintosh hardware. Since it happens on two out of ten Macs in our office, I'm inclined to wonder if there may be a flaw in the machine rather than the software.

(I still want random seed from FMPro)

I've gone back to the software's original incremental method following instructions from the previous post - thank you.

For posterity, just in case it's not entirely clear, you also have to change all your scripts to reflect the new project ID. It can seem relatively daunting if you have a lot of scripts.

I have uploaded the example script here.

I'd be curious to see how many other people arrive at the same problem. I've got FMPRo 7 on Windows XP at home. I'm going to try it there to see if it makes any difference. I've got some other ideas that might work. I hope this helps somebody and look forward to any feedback.

Edit: There doesn't seem to be any problem with the random-shmandom.fp7 on WinXP with FMPro 7 on my machine at home. To test, I just opened the file and hit control-N a bunch of times, closed it and hit control-N a bunch of times again. No errors.

Edited December 2, 2008 by Guest
Follow-up

[comment]

I think you need to report this to FMI. By sheer coincidence (it looks like non-randomness follows you around), someone posted a very similar issue on the FMPExperts list just today - and SOME people were able to reproduce it, too.

It seems that with certain hardware configurations, the random function is seeded "back to square one" every time the application is launched.

[Greenman]

Comment,

Thanks again for your reply. It seems likely it is a hardware configuration problem, rather than a hardware flaw. One of the machines only started showing the problem about a month after the other. Originally, it was just one machine. I have no idea what changed on the second machine to cause the problem. The two macs giving us trouble are different models. No other macs show the same results, regardless of model. Several of the other macs are the same models as those giving us trouble.

By contacting FMI, I'm assuming you are suggesting going to Filemaker.com and filling out a bug report, yes?

[comment]

Yes - if you don't have a more direct support option. I'd say this is a very serious issue, and they should be made aware of it ASAP.

[Greenman]

Done - Thanks, Comment.

[Greenman]

I've created a workaround. It's not elegant, but it works. It involves custom functions. This is how I did it:

First, import the DecToAnyBase function:

/*

DecToAnyBase function



Attribution:

http://www.briandunning.com/cf/218



Related by

'Greenman' on FMForums.com



Format

DecToAnyBase ( decimal ; base ) 



Parameters

decimal - any numeric expression or field containing a non-negative numeric expression.

NOTE: Only the integer part of decimal will be considered.



base - any numeric expression or field containing a non-negative numeric expression.



Data type returned

text



Description

Returns the representation of a decimal (base-10) number in the alphabet of the specified base.



April 20, 2005

*/



Let ( [

/* MODIFY THIS IF YOU WANT TO EXPAND THE FUNCTION TO USE OTHER SYMBOLS

(IF YOUR ALPHABET USES MORE THAN ONE CHARACTER PER SYMBOL, ADJUST SYMBOL SIZE TO FIT) */





alpha = "123456789ABCDEFGHJKLMNOPQRSTUVWXYZ" ;

symbolSize = 1 ;





/* DO NOT MODIFY FROM THIS POINT ON  */

error = 2 > base or base > Length ( alpha ) / symbolSize ;

bit = Mod ( decimal ; base ) ;

pos = bit * symbolSize + 1 ;

char = Middle ( alpha ; pos ; symbolSize ) ;

next = Div ( decimal ; base )

] ;



Choose ( error ;



Case ( 

next ; 

DecToAnyBase ( next ; base )

)

& char



;



"Base must be between 2 and " & Length ( alpha ) / symbolSize



) // end Choose

) // end Let

Then import this RandomNumber function

/*

Random Number function



Attribution:

Based on a function found at this website:

http://sixfriedrice.com/wp/creating-random-numbers-in-filemaker-pro/



Related by

'Greenman' on FMForums.com



Format

RandomNumber ( myMax; MyMin )



Parameters

myMin - any whole decimal number lower than myMax

myMax - any whole decimal number higher than myMin



Dependencies

None



Description

Returns a random whole number anywhere between

the minimum and maximum values provided.



Creation Date

December 03, 2008

*/



Let([myRand = Int ( Random * ( myMax - myMin + 1 ) ) + myMin];

 If( myRand  ≤  myMax and myRand  ≥  myMin ; myRand ; RandomNumber( myMin; myMax ) )

)

Then import this GetOneRandomChar function:

/*

GetOneRandomChar function



Attribution:

Based on a thread at FMForums.com:

http://www.fmforums.com/forum/showtopic.php?tid/199421/



Related by

'Greenman' on FMForums.com



Format

GetOneRandomChar



Parameters

None



Dependencies

DecToAnyBase function

RandomNumber function



Description

Returns one random character chosen 

from the string 'alpha' from the DecToAnyBase function.



Creation Date

December 03, 2008

*/



Let([myNow = Middle ( GetAsNumber( Get( CurrentTime )) ; Ceiling ( Random * Length ( GetAsNumber( Get( CurrentTime ))) ) ; RandomNumber ( 2 ; 5 ))];

  Let([Ocwerx = DecToAnyBase ( myNow ; 34 )];

    Let([Praktik = Middle ( Ocwerx ; Ceiling ( Random * Length ( Ocwerx ) ) ; 1)];

      Praktik

    )

  )

)

Lastly, but probably not necessary, this function:

/* 

NewID function



Attribution:

http://www.fmforums.com/forum/showtopic.php?tid/199421/



Related by

'Greenman' on FMForums.com



Format

NewID



Parameters

None



Dependencies

DecToAnyBase function

RandomNumber function

GetOneRandomChar function



Description

String six random characters together.



Date

12/03/2008

*/



GetOneRandomChar &

GetOneRandomChar &

GetOneRandomChar &

GetOneRandomChar &

GetOneRandomChar &

GetOneRandomChar

The numbers look decidedly non-random, but they're easy to remember and pseudo random. (They seem to have a random sprinkling of decidedly more than one instance of any given character. You'll see what I mean.) And they don't bring up the error that started this in the first place.

I'd like to make the GetOneRandomChar function more random seeming and am looking for suggestions if anyone has them.

Edited: I've incorporated get(CurrentTime) into the GetOneRandomChar function. I probably could use current date, but there seems to be no difference in function.

The reworked file is available here.

[comment]

I'm not sure what is the problem that you are trying to solve. Your random generator is broken on the two systems. Calling the same broken Random() function in another manner is not going to fix it. The only way to "work around" this would be to write your own random generator - which given the limitations of Filemaker's calculation engine is not something I would undertake (in OS X, you could use Applescript to generate random numbers for you).

Luckily for you, you don't need to do any of that, at least not for generating the IDs - because you shouldn't be using random numbers AT ALL in this process.

It seems likely it is a hardware configuration problem, rather than a hardware flaw. One of the machines only started showing the problem about a month after the other. Originally, it was just one machine.

It's also possible that the problem is caused by some software. You should investigate if there is some software component (a plugin, a system utility - anything really) that was installed on the affected machines.

---

BTW, it's customary to quote other people's work verbatim, without deletions or additions. I am sure that was not your intention, but someone just might think you were trying to take credit for these functions.

[Greenman]

Comment,

Thanks for your thoughtful reply.

I'm using an incremental id as the real id. Nevertheless, my client likes that simple six-character reference id. They've been using it for a couple months, and they just like it. This is a klutzy workaround, but my client is happy and the time element introduces enough real-world seeming randomness that it no longer breaks on the two other machines, even if the numbers aren't really random.

As to the functions - thanks for the feedback. I thought attribution would be enough for the first two. Perhaps not. The first script had no Author, and didn't have the character set I wanted. For the functions to all work together, I needed to add that in. I also had to change some of the commenting marks from // to /* */ so it would look right in the forum 'code' bbcoding. The second script had no attribution to it at all, so I added it in. Yes I changed around some things, but still referred back to where I got the information. As to the third, that one is all mine. The fourth is so lame it doesn't really deserve attribution. I put the attribution information as notes to myself so I could refer back to where I got the information or what my thought process was to begin with.

I have not yet heard back from Filemaker about the flaw.

[comment]

I'm using an incremental id as the real id. Nevertheless, my client likes that simple six-character reference id.

Why don't you simply translate the serial number directly into a six-character code? Does anyone care or even take notice of the code's "randomness" from one record to the next?

The first script had no Author

Ahem. The author is listed at the top of the page AND in the code itself. I should know this… :P