Create new record with company info filled in

[dmontano]

I have two tables: vendor_companies, and vendor_employees.

 

In a vendor_company layout I have general vendor company info using numerous fields, then I have an "employees portal" inside of a "employees" tab control on the vendor_company layout that shows which employees are related to the vendor. So far, so good. (If I had to rebuild it - I would be lost.)

 

I have a button inside the "employees" tab, but just below the "employees portal". I want the button to "add" a new employee related to that vendor.

 

So far, I have scripted the button (called "Add Employee") to:

Go to layout: Vendor Employees

New Record/Request

 

Once on this layout (Vendor Employees), a new blank record is created and is ready to receive input to create the new record.

 

Here is what I can not figure out:

I would like the new blank record to have that "vendor's" company information already filled in. The vendor company information already exists in the vendor_companies" table, I just can not figure out how to trigger the data to go into the blank record.

 

I have the "Vendor Employees" layout already set up with all the necessary fields to receive the vendor company data (fields originate from the vendor_companies table). I do have it currently set up where a user can select a drop-down field to select which vendor - and then all the information is filled in automatically. I prefer that the user does not have to make this selection - I want it to auto fill that particular vendors' company info.

 

What I currently have is workable, but certainly not streamlined, efficient, or trouble-free (user could select wrong vendor from drop down). Also, since I created a button described earlier that says "Add Employee", the button is implying that the user will be able to add the employee to that vendor without them having to think about selecting from a drop down.

 

Apologies for being lengthy, just trying to be clear.

 

Any help or comments appreciated.

[David Head]

First of all I don't think you really want the Company data to be copied across into a new Employee record. At least that is not good relational design because you will be storing the company data in several places and it is difficult to keep up to date. So you don't need any Company data fields in the Employees table (if that is what you have). All you need is a CompanyID foreign key field.

 

Second, in the scripted process fro creating a new Employee record, all you need to do before you leave the Company layout is to note the CompanyID for that record. Each Company record has a unique ID doesn't it? You can store the CompanyID in a variable or in a global field.

 

When you create the new Employee record, put the CompanyID into the foreign key field in the Employee record. This will relate the Employee to their Company.

[dmontano]

Hi David,

Thanks for the reply.

 

I created 1 field in the employees_table called "vendor_company_name". That is the only field I created to join companies and employees. My mistake in saying I had all the other fields necessary to hold the company date in "Employees" - sorry for the confusion. I really have all the "other" company fields pasted on the employees layout

 

I do have Primary Keys in each table: pkey_vendor_company_id, and pkey_vendor_employee_id.

 

I also created Foriegn Keys in each table as well: pkey_vendor_company_id, pkey_vendor_employee_id.

 

These keys are not connected though.

 

As for how I joined the tables, I have attached a screenshot that will better illustrate what I have done. I highlighted the tables in question.

 

I have joined these two tables, but not as you described. They are joined as:

vendor_companies_source_table:vendor_company_name

is connected to

vendor_employees_source_table:r_vendor_company_name_for_empoyees_table.

 

To answer your other question about each record with unique_id, the answer is yes.

 

I have not ever done any "variables", however, I am starting to use "global" fields on extremely rare cases. My ignorance of the ramifications of using what I do not know anything about is what keeps me away from using them - plus, I have my hands full trying to get a basic understanding of this program.

 

Thanks

[dmontano]

I am in the process of going back through my posted threads to see what I can work on while my other "stuck" issues fester. So, I am hoping for a revisit on this thread...

 

Okay, I have repaired the ID relationship and I am able to select via drop down the vendors name on the employees layout and it pulls all the data in. It did that before, but I had joined to the company name instead of the ID key. If you say its wrong - its wrong.

 

I used the "stacked" field method as suggested in another thread to call the keyid, but use the other field to show the company name. Seems to be working fine. Really wish there was a more elegant way...

 

Anyway, now that that is done, I would like to get back to doing exactly as you said. You mentioned:

 

"Second, in the scripted process fro creating a new Employee record, all you need to do before you leave the Company layout is to note the CompanyID for that record. Each Company record has a unique ID doesn't it? You can store the CompanyID in a variable or in a global field."

 

I have never done a variable and only a "global" field on very limited basis. If this is something I need to read for a few days - let me know and I won't wait for your response. However, if this is relatively simple to explain - could you explain it. I will make a note, nevertheless, to read up on variable and globals.

 

Thanks again,

David

[HBMarlowe]

I have never done a variable and only a "global" field on very limited basis. ... could you explain it.

 

Hey David!

 

This is actually incredibly easy, and by the time you've done a half-dozen times it will be old hat. While you're on the Customer Layout go to the ScriptMaker. Use the Set Variable script step. Click "Specify." Where it says "Name" type CustomerID. When you leave the field FM will add a "$" so it now says $CustomerID. To the right of the "Value" field click "Specify." Select CustomerID from the list on the left. That sets a variable that is available only for the duration of this script; you can take it anywhere you need it. Next, go to the Employee layout and add the New Record/Request script step. Use the Set Field script step to set the foreign key (probably something like CustomerID_fk) to your variable, $CustomerID. Commit the record, which is now related to the Customer record you started with. All the Customer fields/portals should now be filled in.

[dmontano]

I did something wrong - I get a blank record. I do have a validation on the employee layout that requires the "ststus" pf the employee to be selected. I am prompted to "revert" or No, Yes.

 

So, in the script I built, I turned on ignore validation warning, but still no record with company data. I have posted a PDF of my script steps.

 

What did I forget or get wrong?

[dmontano]

Okay, I got it to work. That's is very cool. Looks professional. Thanks a bunch John. That's 1 down and ahh many to go. I attached the script.

 

I did not know to put the variable into the calc field. Also, I am not sure if it is required, but do I have a go to layout where I want the field variable to be picked up. I do not know if that is needed or not, but if I use this script outside of the context I am currently using it in, I do not think it would work unless I go to layout to pick up the variable???

 

Also, do I HAVE to commit the record for any particular reason? I tried it without the commit and it works. I prefer that because the user needs to enter the name and so forth and then commit. Will the wheels fall off if I leave that script step off?

 

And lastly, when you say I can take this with me anywhere, do you mean the script I set up will always use the company id key in the context of whatever related table or layout I am on?

 

Thanks John! That wasn't so bad - the word variable made my head swirl.

 

I am stoked!

[HBMarlowe]

I am not sure if it is required, but do I have a go to layout where I want the field variable to be picked up.

 

In this case, absolutely. That 's the only way FM can know which record ID to grab. It has to be the active one.

 

Also, do I HAVE to commit the record for any particular reason? I tried it without the commit and it works. I prefer that because the user needs to enter the name and so forth and then commit. Will the wheels fall off if I leave that script step off?

 

No

 

And lastly, when you say I can take this with me anywhere, do you mean the script I set up will always use the company id key in the context of whatever related table or layout I am on?

 

The variable exists only for the duration of the current script. Hypothetically, you could also go to a Note table and use it there to relate a note to the customer.

 

Couple of other points:

1. Your field names are long and (I find) cumbersome. the "vendor_" prefacing each one is completely unnecessary. It's not necessary to name the project, or even the table in each field name. I would name the variable something like $CustID. Much easier to type and to read.

 

2. The GetMessageChoice = 1 step is unnecessary. If the choice is 2 the script never gets to the second test.

[dmontano]

Thanks John,

 

Your additional observation is VERY helpful about field, table naiming, etc.. I have been naming that way as I had been concerned that somewhere down the road - my field names would be the same and that would be a problem (from 1 DB file to another).

 

This is obviously a "language" and your comment helps me toss out what is unnecessary. I have been reading some reference material on the web (not sure what the etiquette is here on the forum so I will wait to post links until I know its fine. It is from the University of Texas at Austin on Data Modeling and it has been a good reference outside of "The Missing Manual".

 

Such as all entities are singular nouns, all relationships are (verbs), helps me with the language.

 

Thanks again,

David

[Ender]

This is obviously a "language" and your comment helps me toss out what is unnecessary. I have been reading some reference material on the web (not sure what the etiquette is here on the forum so I will wait to post links until I know its fine. It is from the University of Texas at Austin on Data Modeling and it has been a good reference outside of "The Missing Manual".

 

Go ahead and post a link to your external source. Then we'll know what you're looking at. It could be good info for the community, or it might not apply to FileMaker.

 

Such as all entities are singular nouns, all relationships are (verbs), helps me with the language.

I also learned to name entities as singulars ("Employee" rather than "Employees"). But the relationship part doesn't really apply within FileMaker's relationship graph (no names on relationships). However, you can use named relationships on your own data model.

[dmontano]

Hi Ender,

 

Here is the link I was referring to:

 

http://www.utexas.edu/its/archive/windows/database/datamodeling/dm/erintro.html

 

It has been tremendously insightful - for me. Hope others can use it as well. It is very apparent to me that this is THE important information that is really not presented in the FM books I have. They do discuss - but not at this length.

 

Not saying I understand it, but at least I have something to read and pretend I am understanding it.