Auto create entry in a related table

[mcschan]

Hi all,

Thank you for helping me here.

I am creating an asset db for the company I am working for. I have an Assets table containing a field called Model Number and a Computer Types table containing a Model Number and a Model Name field. Asset::Model Number = Computer Types::Model Number, and the "Allow Creation of Records in this Table via this Relationship" is checked in this relationship.

I put the Asset::Model Number and Computer Types::Computer Name in my Asset Details layout. The Computer Types::Model Number field is NOT shown to the user at any place. I validate Asset::Model Number against the Computer Types::Model Number (as a value list) when the user enters a value. If the user enters a value that's not in Computer Types::Model Number, FM will prompt the user to enter a model name (Computer Types::Model Name), hence a new record is created in the Computer Types table. The trouble is, if the user doesn't enter a model name, then even though a model number has been entered, there won't be a new record created for the model number in the Computer Types table. I want to be able to add a new record even without the user entering a model name in the Computer Types::Model Name field.

Please advise me on this dear FM experts. Thank you very much.

Matthew

[LaRetta]

Hi Matthew,

For FileMaker to use the 'Allow Creation of Related' option, you must type into a field in Computer Types that is not the Computer Types Model Number (or what the relationship is based upon). You could use a field in Computer Types called OverrideName (number), for instance ... that they need to check if they don't want to put a name. This would create the record also.

Or you can script record creation. In truth, do you want to accept a Model Number in Computer Types with NO information whatsoever? I would think something other than the Model Number should be required anyway.

LaRetta

[mcschan]

Dear LaRetta,

Thanks for replying.

The reason for recording the model numbers even without a proper computer types name is because I want a count of the unrecognized computer models.

I tried writing a script for creating the entries. The Asset::Model Number is a text field, not global. So in the Assets table, I created a field called Asset::Temp Variable, which is global. In the script, I did a SetField[Asset::Temp Variable; Asset::Model Number] and then a SetField[Computer Types::Model Number; Asset::Temp Variable]. But I don't know why the value of Asset::Model Number didn't copy into Asset::Temp Variable?

Thanks

Matthew

[LaRetta]

Hi Matthew,

A global in Asset which is script-set using Set Field [ Asset:temp Variable ; Asset:Model Number ] should work. Even if you have just created a new record in Asset and have not committed the record yet. With Set Field[] you don't even need to be on a layout based upon Asset. You are saying you can't set a global? Too strange! They always work - they don't need relationship or anything!

But setting the global is only part of it ... Since you are using your Model Number as the unique ID for the relationship, you will need to take some action to create the record, using one of the following options.

1) Fire script to set your global Temp Variable with the Model Number, go to a layout based upon Computer Types, create a new record and set the Computer Types Model Number to Asset:Temp Variable.

2) Fire script from User button click, which captures the Asset Model Number as script parameter, go to Computer Types layout, create a new record, and Set Field [ Computer Types:Model Number ; Get(ScriptParameter) ]

3) Create a field in Computer Types called 'No Name' checkbox. Place this checkbox on your Asset layout and User will need to click it (which will create the related).

4) Change your relationship to be based upon unique system-generated IDs joining ID = ID and turn on 'Allow Creation.' There are many reasons for using a unique ID (or serial). Most developers use this method. One of its benefits (there are many) is the ability to create related without need of action (script or User intervention).

Create ID (or serial) called AssetID in both tables, joining on =. Then typing in the Model Number would create your related for you.

If you wish to still use TempVariable and you can't identify why it won't set, you may need to attach your file. :

LaRetta

Edited July 16, 2005 by Guest

[Søren Dyhr]

I struggled for quite some time to get the my head around what you want ...this resulted in this template - I wish to know is somewhere in the vicinty of you'd asked for ...what puzzeled me that you could make a new portalrecord in another parentrecord... until i realized that it must be only an interface record, not belonging to a particualar normalized structure, but instead was the entry that determined the organization in an otherwise pretty flat structure.

Please let me know if I'm nowhere near what you had in mind???

--sd

[LaRetta]

Soren, your file won't download for me. I'm interested in seeing your idea. Can you try attaching it again? Thank you! :

[Søren Dyhr]

Sure, it might be the new permission to upload .fp7's that are a little wonky yet??? Or that I havn't waited until the display changed ...oh dear how this php is cool almost realtime!!!

--sd

doi.zip

[LaRetta]

That worked thanks! It's fun to consider this idea.

I've downloaded six files tonight (errrr, this morning) and they all worked but they all were zips.

Yeah, php is cool ... particularly FX.php! It'll be fun learning it. :