Auto-create record and auto-enter

[Jacob22x]

I have a layout that allows users to enter in reports - the layout is based on the table "report" and has a portal to the table "issues" which holds all the line items. So the user fills out the report header information once and then fills out all the line items in the portal. One of the fields for each line item is discipline (mechanical, electrical, etc.) and one issue can have multiple disciplines.

I have it set up with two more tables. The first table, "discipline", just has the eight disciplines, an ID number and a graphic for each one. The other table, "discipline join," contains a foreign key for each issue (IssueID) and a foreign key for each discipline (disciplineID).

What I need is that every time someone creates a new issue through the portal in issues, it auto-enters a value in the "discipline join" table, so that every issue is associated with a discipline. That means the system needs to auto-create a record in "discipline join," since creating a new record in "issues" doesn't necessitate a new record in "discipline join." Any ideas?

[CobaltSky]

Hello Jacob,

There are several possible ways to achieve what you've described, but all of them will involve some user action to define which discipline(s) should be entered into the 'dicipline join' table for a given issue - so it can scarcely be 'auto-entry' (unless you want the same default discipline assighned to each new issue).

One fairly easy way to set it up would be to provide for the user to enter one discipline into the issues portal on the reports layout and (if there are additional disciplines for a given issue) to click a button to go to the issue layout to add second and subsequent disciplines for an issue. I'm attaching a quick demo to show how that might work.

Alternatively, you might have the user select multiple disciplines in the reports portal, then when they are done, cl;ick a button (which launches a script to create the appropriate corresponding records in the discipline join table).

Hope that gives you some ideas to work with.

Reports.zip

[Jacob22x]

Ray,

Actually, i already have it set up similar to what you say - they can pick one discipline from the issues portal, but if they want more they click on a button that takes them to another layout so they can add more. But, you mention adding the same default discipline assigned to each new issue, and that is what I want. I want it so as soon as they start entering information on a new issue, discipline is automatically assigned as "general," and they can change it if they want. But the auto-enter feature doesn't work because discipline isn't a field in the "issues" table, but in the related "discipline join" table. I'm fine with creating a script for this, I just don't know how - basically because I don't know how to get a script to run on the creation of a new record within a portal. Thanks for the attachment and the help!

[CobaltSky]

Hi Jacob,

You're correct that getting a record created in the join table (with a default discipline value) using your present structure will require that a script be run. There are a number of ways forward. Here are three of the options:

1. Redefine your structure to use a multi-key field in the issues table to specify disciplines, rahter than a join file, then set an auto-enter option to place the 'general' disciplineID code into that field whenever a record is created.

2. Use a script triggering plug-in to fire off a Script every time a new serial number is created in the issues table. You could use a free plug-in such as the one available at http://homepage.mac.com/jkornhaus to do this if you wish.

3. Turn off the option to 'Allow creation of records in this table via this relationship' option for the existing relationship from Reports to Issues, then require users to run a script (eg click on a button) to create new rows in the issues portal.

In case you decide to use the third approach, you will find a further example file attached which shows one of the methods you might use when setting up the script.

Reports2.zip

[Jacob22x]

Thanks so much - those are all pretty good options. I decided to go with the plug-in solution, since I've actually been looking for a plug-in to do something like that anyway, but I'm having trouble making it work. If I could bother you one more time, do you know how to properly implement that plug-in? I set one of my auto-enter serial numbers to also have a calculated value, which calls the plug-in function, and have it call a script that sets the displine field to "general," but nothing happens when I create a new issue. No error message comes up, either, though.

[CobaltSky]

Hi,

It's hard to say what is going wrong without knowing a whole lot more about how you have set it up.

I suggest that you have the calc set up to trigger a simple test script that posts a dialog first, so that you can troubleshoot the triggering process on its own first. Once you are satisfied that the script triggers appropriuately, then re-assign the calc to trigger your more complex scritp. Otherwise it is not clear if it is the trigger or the script which is failing.

Once you narrow the field down in this way, I suggest that you post either the calculation or the script (whichever one it is that is failing) in full so we can have a look at it. Then there is some chance that someone here will be able to spot the reason why it doesn't behave as you want it to...

[Jacob22x]

Just wanted to let everyone know that I figured out how to solve the problem - I didn't use the plugin that Ray suggested, but I found a similar one online at databasepros.com (it's the same plugin that ships with developer 7, which allows a script to run on the exiting of a field). So now when a new issue is entered in the portal, the database runs a script that sets the discipline field to "general." Awesome. Thanks for the help, Ray.