I know it's not possible to use a field with multiple entries as a link field directly but I wondered if there's a scripting way to achieve a similar effect?

I have a database with multiple entries in a field (field is called "Reference No")that we use to book batches of new items in (i.e. one's that are not yet with our department but are due in)
We then have a database with items that are with us (i.e. have arrived and are on shelves) The problem is the departments sending items to us keep re-using the old references from previous deposits which they shouldn't as it means we have to manually change the References written on each item when we realise.

I need a way for an alert or an auto fill to be automatically occuring on the booking in database when an entry, already on the arrived database, is inserted in the Reference field. It can't be a single entry field as in the booking database each record is a day and we have lots of arrivals per day.

Hope that makes sense! Has anyone got any suggestions? I've been trying to think about Recordsets but the Help doesn't seem to have an example and I haven't used them before.
Or perhaps there's a more simple answer I've missed?
Thanks!

Oh yes, it would be easy to look up the entered reference number in ArrivedDb.RefNo via recordset from BookingDb. (I'll use ArrivedDb and BookingDb to refer to your two databases.)

Here's some sample code you could use to look up the ref number. I haven't tested it or anything.

  


/* example of code in use */
function myButton_onClick()
{
	var refNo = Form.boxes("myBox").content; // assumes single entry, split into separate entries as required
	var refNoExists = ContainsReferenceNumber(refNo, "Reference No", "ArrivedDb", "");
	if (refNoExists == true)
	{
		Application.message("Alarm. Alert. " + refNo + " exists in ArrivedDb.");
	}
}

/* the handy code */

// returns boolean (true/false)
function ContainsReferenceNumber(referenceNo, field, textbase, password)
{
	var output = false;
	try
	{
		var rs = Application.newRecordset(textbase, Application.activeTextbase.path, password);
		if (rs == null)
		{
			throw new DbtextException(textbase, "Unable to create textbase recordset.");
		}
		else
		{
			var commandQuery = "find (" + field + " =" + referenceNo + ")";
			rs.Open(commandQuery);
			if (rs.Errors.Count > 0)
			{
				throw new DbtextException(rs.Errors(0).Source, rs.Errors(0).Description);
			}
			else if (rs.RecordCount > 0)
			{
				output = true;
			}
			rs.Close();
		}
	}
	catch(err)
	{
		Application.message(err.name + ": " + err.message);
	}
	return output;
}

function DbtextException(name, message)
{
	this.message = message;
	this.name = name;
}

That is absolutely wonderful Peter - I can't thank you enough!!!
Ax

Sorry, just one thing - it doesn't seem to work for the multiple entries. It works when I type in RS4/80 (telling me it's duplicated) and when I delete that and type in RT8/901 (which it tells me nothing as it's unique) but when I put:

•RT8/901
•RS4/80

It tells me nothing. Can I make it come up with an alert under these circumstances or is that not possible?(incidentally if I put RS4/80 in the field first it alerts me) If not I guess I could script the last entry to a blank field and test it's unique from there but it is a bit long-winded

Yeah, I put a note in the MyButton_onClick() method there to indicate where you should handle multiple entries.

Split "myBox" content into an array of entries on the Application.entrySeparator character. Loop through the array and run each entry through the ContainsReferenceNumber method.

 
function myButton_onClick()
{
	var refNos = Form.boxes("myBox").content.split(Application.entrySeparator);
	for (var i = 0; i < refNos.length; i++)
	{
		var refNoExists = ContainsReferenceNumber(refNos[i], "Reference No", "ArrivedDb", "");
		if (refNoExists == true)
		{
			Application.message("Alarm. Alert. " + refNos[i] + " exists in ArrivedDb.");
		}	
	}
}
 

Thanks Peter - not seeing the wood for the trees!
Ax