Forcing entry of a portal record upon saving of parent record

[Steven Swallow]

For all intents and purposes I have two tables. 'tblCompanies' is a straight record of company details (UID = CompanyCode), and 'linkBusinessAreas' is a table containing business area codes stored against CompanyCode as the relational field...so one to many, tblCompanies to linkBusinessAreas.

 

Now, as a company can have multiple business areas, I have it set up so when entering a new company, there is a portal to 'linkBusinessAreas' and records can be added that way. This works fine.

 

What I can't figure out is making it mandatory to have at least one business area entry for the newly entered company record. At the moment, it's perfectly possible to enter a new company with no business area records stored in 'linkBusinessAreas'.

 

I need some help figuring this out. I'm sure this is another question I've asked that has been asked before, but again, my searching has turned up nothing...maybe I'm using the wrong terminology.

 

Hope that all makes some sort of sense.

[No_access]

Under Validation you can use not isempty()  in the validate by calculation ?

[Steven Swallow]

I'll have a look at that, although I can pretty much get around most of FileMaker's click and drag functions, much of the underlying 'programming', syntax and script writing goes over my head, so I may need further help, but I'll see if I can get it going myself first.

 

Thanks for the pointer...I may be back!

[Steven Swallow]

I said I'd be back.

 

Right my understanding of the use of 'not isempty(field)' is non existant. However, I take it to essentially mean 'requires content' which makes sense from the validation perspective...but then there's a 'not empty' check-box for that so I'm not sure how this is supposed to help.

 

The problem remains that I can enter & save a new company record without having to enter an associated business area record in the relevant portal. I tried adding the following into the validation of the 'BusinessType' field in the portal table to no avail.

not IsEmpty ( BusinessType )

I really don't know what I'm doing with this...

[Steven Swallow]

OK...tried again.

 

I put the following into the validate by calculation part of the UID of the main company record (which is an auto generated serial number).

not IsEmpty ( linkBusinessAreas::BusinessType )

This seems to work as I'd hoped, but I do wonder whether it's good practice. It just seems a bit 'wrong' to have a validation message on the UID for this. Although it will work because the company record UID must always exist, now the error trapping is looking at something else instead of the UID itself.

 

I can see this method may become more problematic if I need to add further data entry portals (which is likely).

 

Unless of course I've completely misinterpreted the way you wanted me to use this check, I'll be needing a different method in the not too distant future, but it will work for now.

 

Thanks for your help.

[Steven Swallow]

Apologies for resurrecting an old(ish) post, but I'm still having some problems with this and am trying a different approach.

 

Instead of the above, I've now created a calculation field that counts the number of linked records in 'linkBusinessAreas', the idea being that if the result of that count is zero, then it'll prompt for entry of the business area in the portal...and that's the bit I'm stuck on.

 

Now I've got the count, I don't know what to do with it!

 

I never liked having the validation in the UID as above for the reasons I stated before, and would like to work out an alternative way...

[Wim Decorte]

It is more of a workflow issue than anything else; you can write a script that checks for related records and signals if there are none.  Then in the navigation away from that layout (or record navigation) you run that script and stop the user from doing anything else.

[Steven Swallow]

I've finally figured out a way to do it with a simple script (everything's simple once it's figured out)!

 

Essentially I've used on OnRecordCommit script trigger of the layout. An 'If' statement checks the count field, and if it's empty (or zero) it pops up with a custom dialog, then places the cursor in the portal field ready for entry.

 

I then had to add another line 'Exit Script [Result: False]' so that the record didn't actually commit.

 

These seems to work fine. I'll be wanting to move away from the custom dialog at some point as I'm looking at WebDirect and unserstand they don't work, but at least I have the logic now.

[eos]

 

Essentially I've used on OnRecordCommit script trigger of the layout. An 'If' statement checks the count field, and if it's empty (or zero) it pops up with a custom dialog, then places the cursor in the portal field ready for entry.

 

You don't need an extra (and otherwise useless) field for this; instead of checking that field's value in your script, perform its calculation in the If[] step.

 

 

I'll be wanting to move away from the custom dialog at some point as I'm looking at WebDirect and unserstand they don't work, but at least I have the logic now.

 

If it's mandatory to have at least one business area, why do you use the dialog at all, instead of simply adding that first related record?

[comment]

You may not be aware enough of the differences between field validation and script triggers. Field validation is the tool that ensures the integrity of your data; it works at the data level, and does not depend upon the presence of any layout object and/or upon the successful running of a script.

 

 

It just seems a bit 'wrong' to have a validation message on the UID for this.

 

There is nothing wrong with that; you can attach such validation (that a related record must exist) to any local field - and since the primary key field is the one that's least likely to be deleted, it is a good choice for such task.

 

 

Of course, you can (and given the unfriendly nature of validation error messages, probably should) employ script triggers in addition to field validation - but not instead.

[Steven Swallow]

I understand the difference between field validation and script triggers. I use field validation all over the place to make sure telephone numbers are formatted correctly, email addresses are all lower case and have an '@' symbol and so on and so forth.

 

My weakness is in the scripting side of things, so when it comes down to a problem that I'm struggling to solve (such as this one), I may not know the best way about doing it, and generally scan the script options to see what looks likely to help. As an example, this is the first time I have scripted an 'If' statement...I don't know how to count and check a field in a script, but I do (now) know how to check an already calculated field using a script. Might not be neat and tidy, but that's how I worked out how to do it.

 

I also don't know how to add a portal record via a script...I don't see how I can do this without knowing what it is the user wants to enter. I did experiment with script steps involving NextPortalRow (or something similar) but didn't get anywhere, so decided to give the user a nice message, then navigate them to the relevant portal field to get started.

 

Is this wrong, or is it just not how you'd do it?