Hi All,

I am having a very strange (to me) problem in filemaker 7.0v3 on Mac os 10. I am creating a recipe database to keep track of ingredients and foods which go well together.

I have created a script ("Create New" on the Ingredient Edit Layout of the "Recipe Interface" file in the attached files) to add matching ingredietns to a join table. All is working great except for the validation step. I seem to be having very weird behavior. I have created an auto-enter field which is supposed to concact the ingredient1 primary key with the Ingredient2 primary key. The field is to be validated unique. What I want to have happen is for the script to check that validation did not fail. If it did, I want the script to alert the user and then delete the created record and gracefully end the script.

Unfortunately, what is happening is the following. 1) For some reason, the autoenter does not alway work when the data is entered in the interface file (the base table is in the other file, "recipe master"). I have tried to chage the autoenter to a "set field" step occuring in the script right after ingredient1 and ingredient2 are set in the join table. However, if "set error capture" is set to "on" the set field does not work.

Even when both work, I can not seem to get the If "Get (LastError)" step to trap for error 507 to work. It seems that this should be easy, but I am getting very confused.

I hope I have been clear. I have attached the files in question in case that might make it more clear. Thanks for any help.

Don

Change Validate data in this field to Always.

Hi Thanks for your reply,

I tried that, but it did not seem to help. It appears that the problem is that when the script tries to set the field or the auto enter tries to set the field, the behavior is aborted and skipped if this will cause the validation to fail. As a result the error traping is not noticing the error becuase the program has avoided it. Any ideas how to work around this?

In one of your prior posts you gave the following suggestion:

"Relate the two files by the appropriate fields. Add a calculated field to the first which does a lookup to the second. Set it so that the field results in zero if there is no match, 1 otherwise. Delete all records with the value of 1 in that field."

Would that work here? If so, could you say more about how you might do this? Thanks,

Don

That would work (but seems a little extreme here). The calculation would be done in validation.

Try to read this article and especially the part "When Validation Occurs" in this:

http://www.informit.com/articles/article.asp?p=337156&seqNum=4

--sd

Hi, Thanks for all of your responses,

I read the article suggested (it is a very good book), and found that what I suspected is likley what is happening...the auto enter to concat the two id's is failing and being skipped because it would cause an invaldid condition.

I tried doing it the other way ("Relate the two files by the appropriate fields. Add a calculated field to the first which does a lookup to the second. Set it so that the field results in zero if there is no match, 1 otherwise. Delete all records with the value of 1 in that field." ), and it worked but it caused other problems which I am now trying to sort out. I would love to find a way to trap for the existance of a duplicate when combining the two Id's without commiting the record (so that I may revert the record without having to delete, which causes a cascaing delete).

I feel like I am going in circles, but I am learning as I go so I don't mind.

Hi All,

title

I have managed to solve to problem. I used a technique described by Queue ]http://www.fmforums.com/threads/showflat...Post112623/url] and ).]http://www.fmforums.com/threads/showflat...ost85361/url]).

The technique uses " not IsEmpty (ctrl..DupesCheck__ingredient.FlavorMatches::zd_ingredient1)" to test for the existance of a related record. Though I don't totaly understand it, Queue also describes how this technique can be used in a multi-user sitatuion to test for a related record and then either create a new record or edit an existing record.

Thanks again for all of your help.

Don

P.S., Add to the list of things I still need to learn is how to correctly format the urls so only the title shows up. I tried, but figured after three attemps that there were other things to do.

I've thought 'bout it - You might do it another way, try to investigate this uploaded template!

--sd

IngredienceMIX.zip

It just occured to me that the reversed order needs to be investigated by yet another TO - So dear downloaders do you need a new template or can you fix it right away???

--sd

Hi Soren,

Thanks for your reply, sorry I have been away for a few days. I was trying to implement the suggestions you guys provided.

At first all was working well, I had used the technique described above " not IsEmpty (ctrl..DupesCheck__ingredient.FlavorMatches::zd_ingredient1)" to test for the existance of a related record. This was working fine. I then decided to take the next step of implementing a floating pop-up selection box as that was the final intent. It still will always alert me if the ingredient pair had been previously set, however, it also now gives false positives.

What happens is this: If I select eggs and butter as the match the first time (never before seleted) a match is made. If I delete the comination and commit the records in both tables and select the same pair the alert message comes up warning that the pair already exists as a combination (even though the previous pair has already been deleted, and all apparent evidence of this previous pair has been wiped clean.

You mention that the reversed order needs to be investigated by yet another table occurence, I am not sure if that would help this situation, but I am open to any suggesetions.

Out of currosity, in the example you posted you used a similar relationship to test for the occurence of a related record but used "Count ( Untitled 2::Item1 )" instead of "not is empty". Could you say more about how this function works.

In case it is helpful, I have attached images of the relationship graph and pdf's of the two scripts working to perform this function. The first "create new record" is activated when the user selects the button to create a new matching pair. The second, "selection window open" opens the floating selection window and then sets the globals for comparison based on the user's selection.

As always, I thank you for all of your help.

Don

Archive.zip