Calc field to fetch data from other file

[philipcaplan]

I have two files called "signup.fmp12" and "2000-words.fmp12". I am using FMPro16 on MacOSX 10.13.3.

"2000-words.fmp12" has ABOUT 1950 records with only 2 text fields: one called "serial_number" and the other called "word". Each contain unique values.

I want to add a calculation field called "pword"  to "signup.fmp12" which fetches one random word from "2000-words.fmp12"

So each record in "signup.fmp12" will have one randomly-different word in the field "pword". (Strict randomness is not essential)

I am unsure how to "connect" these 2 files so that this can be done.

Could some kind person please give me some nice clear "idiot-proof" instructions on how to do this. and how to create my "pword" calculation field.

(Note: the unique "serial_number" associated with each unique "word" in 2000-words.fmp12 may be of help in this calculation, or it may be quite un-needed!!)

[comment]

First thing: if "pword" means "password", I suggest you rethink your overall strategy. 

Now, if you want the signup.fmp12 file to select from a table in the 2000-words.fmp12 file, you must link the two files, either by creating a relationship or - perhaps preferably in this case - just define a value list in the 2000-words.fmp12 file as using values from the "word" field, and another value list in the signup.fmp12 file as using values from a value list in another file. You will be prompted to add  2000-words.fmp12  as a data source, and after that you will be able to select the first value list as the list to use.

Selecting a random value from a value list is just a matter of:

Let (
values = ValueListItems ( "" ; "MyValueList" ) 
;
GetValue ( values ; Int ( Random * ValueCount ( values ) ) + 1 )
)

You probably want to use this in an auto-entered calculation for a Text field, not as a calculation field.

 

Note:
If the 2000-words.fmp12 file is open, you can use its value list directly, by replacing:

ValueListItems ( "" ; "MyValueList" ) 

with:

ValueListItems ( "2000-words" ; "FirstValueList" ) 

 

---
P.S. With only ~2k values, you could just paste them into a custom value list defined in the signup.fmp12 file and save yourself the hassle of managing two files.

 

 

Edited April 25, 2019 by comment

[philipcaplan]

Thank you, comment. That worked perfectly!!

Now, I'll be grateful if can you also help me with the following: to create a random number from 1 to 99 but excluding "zero" and "1" (ie, only using 2,3,4,5,6,7,8,9).

Many thanks.   Philip

[comment]

To generate a random integer between 2 and 99 (including 2 and 99), use =

Int ( Random * 98 ) + 2

 

 

Edited April 25, 2019 by comment

[Fitch]

My take on it is that he didn't want any numbers that have 0 or 1. So maybe something like this:

Let ( [
nums = List ( 2;3;4;5;6;7;8;9;22;23;24;25;26;27;28;29 ) ;  // etc.
vCount = ValueCount ( nums ) ;
r = Int ( vCount * Random ) + 1 ;
result = GetValue ( nums ; r )
];

result
 )

 

[comment]

5 minutes ago, Fitch said:

My take on it is that he didn't want any numbers that have 0 or 1.

What would be the purpose of that? 

[philipcaplan]

Fitch is correct. I wanted the random number to never contain the digits "0" or "1" because when they are displayed to a user of my website, they can (depending on the font used and other factors) be confused with the letters o or O or l or I (the last two were "l' as in lemon, and "I" as in India, thereby proving my point of wanting to avoid possible confusion!!).

I tried using his formula above [from Let( to result)] and it seemed to work OK.

I then tried the following (which was in an email sent from this thread to me in Fitch's name, although I don't see it on the site!!):
Filter ( GetAsNumber ( Int ( Random * 10 ) & Int ( Random * 10 ) ) ; "23456789" )
and while it also seemed to work, after producing a number of good results (such as 23, 67, 94, etc) it then gave a blank result!! Presumably both random numbers generated on that occasion resulted in 01 or 10 or 11 -- and so the entire result was "filtered" out!!

So, I am using the Let(...result) formula -- although it will probably be far more difficult for (porr little me) if I need to come back and try to understand it in the future!!!  In fact, looking at it now, it looks as though it currently only generates numbers up to 29, and I would have to type into it all the other possible numbers to get up to 99. Fitch chickened out of doing that!!!

And, since I now feel that 2 digits looks better (more regular) on my site than just one digit, perhaps what I really want is a number between 22 and 99 (excluding any that include "zero" or "one"). Of course, I can put all the numbers into the Let...result) formula to achieve that.

Or is there a version of the "Filter" formula that would work to always guarantee two digits as a result (with no 0's or 1's)?

[comment]

I would still want to know what is the purpose of this random number, before suggesting a solution. This is because some solutions are "more random" than others. For example, you could substitute the digits 0 and 1 with a random digit 2-9 - but this might result in some numbers being selected more often than others (I haven't analyzed this through, but in a critical application it needs to be analyzed).

 

ADDED:

On second thought, I believe the following is as random as it can get, and rather simple =

GetAsNumber (
Middle ( "023456789" ; Int ( Random * 9 ) + 1 ; 1 )  
& 
Middle ( "23456789" ; Int ( Random * 8 ) + 1 ; 1 )  
)

 

5 hours ago, philipcaplan said:

perhaps what I really want is a number between 22 and 99 (excluding any that include "zero" or "one").

Then you can use just =

Middle ( "23456789" ; Int ( Random * 8 ) + 1 ; 1 )  
& 
Middle ( "23456789" ; Int ( Random * 8 ) + 1 ; 1 )  

 

 

Edited April 26, 2019 by comment

[Fitch]

Philip -- I "chickened out" of doing all your work for you? Wow. You're welcome.

The reason you got the other email was that after I posted that formula, I realized it would fail if both Random calcs landed on zero, as you discovered. So I deleted the post.

[philipcaplan]

Hi Fitch. Sorry if I offended you. Bad choice of word. Please accept my apology.

 

Thank you comment. The latest one seems to do the job I need, and as you say it is simple (as per Occam's Razor, I think).

True randomness is not essential. I am using these calcs to generate a password to give to users of a website I am trying to create, with a format of word99word (ie, 2 "random" words separated by a 2digit number).

What I am seeking is "close to uniqueness". With 2000 words the chances of the same password being generated twice I calculate at 1:256million - good enough I think. This is similar to the password that AOL gave me about 20 years ago, which I can still remember!! So many passwords since then!!!

If I start getting users, I can pay a freelance programmer to reproduce the technique **plus** a check that the generated pword is not already in use, as well as other parts of the "signup" process which I am currently prototyping by pasting the results of an online hosted form into my FMPro database to extract name, email, etc, and then email them confirmation with the password to use.

Thanks once again for all your help.

[comment]

3 hours ago, philipcaplan said:

I am using these calcs to generate a password to give to users of a website I am trying to create

Allow me to repeat this:

22 hours ago, comment said:

First thing: if "pword" means "password", I suggest you rethink your overall strategy. 

This technique may be fine to generate an initial password for new users or users that forgot  their password. However, storing users passwords is an extremely bad idea. This is not a Filemaker-specific matter - do some reading.

[philipcaplan]

Thank you comment - I understand and agree with what you are saying.

This **is** "an initial password", but you are correct that if my site is successful, I will need to have it programmed so that all passwords are stored as for example a hash of password/username-or-email. I do understand that.

I have done a fair amount of reading on this matter. In fact, it is now such a well known technique, that I am amazed that there are still any databases of plaintext-passwords floating around in the world!

[comment]

Good. 

Although this may be somewhat off-topic, I will point out one more thing:

10 hours ago, philipcaplan said:

What I am seeking is "close to uniqueness". With 2000 words the chances of the same password being generated twice I calculate at 1:256million - good enough I think.

I am not sure why it's important for each user to have a unique (or "close to unique") password. However, I am quite sure that your calculation is wrong. True, with 2 words drawn out of 2,000 and 2 digits drawn out of 8, the total possible number of distinct passwords is 256 million. However, the probability of two users getting the same random password increases dramatically with the number of users. This is known as the birthday paradox: although there are 365 possible birthdays, in a class of only 10 students there is already a 11.7% chance of two students having the same birthday. Double the number of students to 20 and the probability jumps to 41.1%.

Calculating the odds in your example is rather difficult because the numbers are too large for a personal computer. I've made some approximations that predict that with 2,000 users your chance of getting a duplicate password is almost 0.8% - that's 2 million times higher than your estimate of 1:256M.