So simple, but having difficulties...

[macmangler]

OK... first, many thanks for all of your contributions to previous posts. I swear I am starting to figure this stuff out!

Now, I have a new dilemna.

I have multiple databses. In this example, I have a contact database (Company, address, contact info, yadie yada yada) and maybe a Job Ticket, requiring similar fields from my contact database.

Lets say I do a find on my contact database... and I find my contact, John Doe @ Doe, Inc.

I want to make a button (suggestions please), that on this "current record," I want to make a a new job on my Job Ticket Database with all of John Does info in place already.

I feel like I have tried so many combinations with scripts... and nothing is working. BUt somehow, I know this is what filemaker is for.

So I ask the experts...you guys.... what advice do you have?

I tried scripts... like copy field from contacts, open job tickets, make new record.. paste one field into another, blah blah blah.

Nothing seems to work. Temporarily, I have pull down menus and my databases are working well. On some levels though, this is very manual... evem though it really is not, if that made sense.

Any input is appreciated. I have a good grip on my relationships between databases... but now I am challenged.

Oh, for those of you who read my "Crazy Idea" Post... thanks, the advice I chose, was forget about until I solve all my other "necessary" issues.

Regards...

Mike

[macmangler]

OH... I did upgrade to FM8 Advanced.... what a trip from FM6!

[Mike D.]

The easiest way would be to put a portal on the contact layout related to the Job Ticket table. Make sure that the relationship allows new records to be created in the Job Ticket table. When you create a new job in the portal, it atomatically associates that job with John Doe.

Mike

[macmangler]

I just started playing with portals... don't think that was the way to go. I did just kind of learn something anout scripts...

It seems like when you write a script that opens up another database, the script virtually ends. I think my answer is a script..

Contact Script...

1. Copy Field Company

2. Go to Job Tickets Databse

3. Permform Script

Then that script is...

1. Create new Record

2. Go to Field "Company"

2. Paste

3. End Script

This seems to work. When I did it before... I tried to everything in the first script... and as soon as it opened the other databse, the script basically died.

So, I ask, is there another way? Or is this the best way, or only way?

I hope that made sense.

Many Thanks,

Mike

[Brian C]

Keep in mind that for a copy and paste command to work in a script, the fields you refer to MUST be on the layout you are using in order to function at all.

Setfield() is a much better command to use 99% of the time.

Try this instead:

## Source Table Script

perform script (Script: tableOccurance::scriptname , Parameter: tableOccurance::company)

## Destination Table Script

Enter Browse Mode

New Record/Request

setfield(tableOccurance::company , get(scriptparameter)

[Razumovsky]

Play some more, portals are the way to go. At least explain why you think they are not.

Assuming your relationship from contacts to Jobs is ContactID::ContactID, all you need to do is click "allow creation of related records" on the Jobs side of the relationship box in define fields.

Now, your portal based on the relationship will always have one extra row in it. If you enter data into any field in this row, it will automatically create a new record in Jobs and assign it with ContactID.

You do not need to have any contact info but the contactID in Jobs. You can access/display all this info from the original contacts record over the relationship. If for some reason you needed to archive this info (if you did not want changes in address in contacts to change the address in Jobs), you would just recreate these fields as lookups in Jobs table.

Copy/paste and multi script approach will give you indigestion, and is pretty clunky. Portals are a great tool of convenience in FM, and designed with this type of task in mind. Get comfortable with them now and you will be much happier.

-Raz

[Mike D.]

Mike

Two suggestions:

1 - Unlike FM6, with FM8 you can have multiple tables in a single file. I would put both your Contacts and Job Tickets tables into the same database. You would also see the advantage of using a portal with this design.

2 - If you are going to use the script approach, don't use the copy/paste method. Your script should use the Set Field step instead. You will have a greater probability of problems down the road if you copy/paste.

Also, I get the impression you are using the Company Name as a "Key" field. You should really assign a number field to each company (i.e., CompanyID). I would recommend that it be auto-assigned and not visible to the user. Use that field for your relationships. Do the same for the Job Tickets.

Mike

[Mike D.]

Mike - Are you feeling bombarded??

[Brian C]

Well we are helpful in any case :)

[Razumovsky]

Yikes, well at least we didnt all contradict each other (directly).

I would put both your Contacts and Job Tickets tables into the same database. You would also see the advantage of using a portal with this design

I dont see how portals work any different, easier, or better in a single file over a multiple file DB. Scripts, yes, but portals? Please do explain.

-Raz

[Mike D.]

Raz,

You are correct there is no difference for portals if you are using a single or multiple files. It is just easier for someone like Mike Bognar to understand when the tables are in the same database.

Yes, we are all in agreement.

Mike

[Brian C]

There isnt much difference speed-wise past the initial call to open the separate file when the portal data is accessed (provided you dont have something that causes your other file to open ahead of time).

The only other real reason why is that it makes account management easier - instead of changing a password on multiple files, you only need change it on one file.

[Razumovsky]

OK,

Just wanted to make sure that people didn't get the impression that Portals were only a good idea in single file solutions. The advantage of using a portal should be seen regardless of how many files there are.

-Raz

[Søren Dyhr]

The advantage of using a portal should be seen regardless of how many files there are.

Wheew! What a relief to be ensured!!!

--sd

[Razumovsky]

Yep, you were the one I was worried about, Mr. Portal-shy... :P

[macmangler]

Time out everyone! (Thanks for all your feedback)

I agree on ID # field, actually that is what I am using, but tried to make things more understandable in a post. #'s are much more logical and do not conflict with multiple contacts within one company. Unique #'s separate everyone from everyone... simple and easy.

As for keeping contacts and jobs in one database.... I am a novice, but do disagree. First, my contacts are around 800 records, but my jobs range to about 15,000 records. More logical to separate jobs and contacts using separate databses. Also.... everyhere I have ever read suggests to keep separate databases for every possible calculation, probably due to backing up and recovery and also as not to slow down the network, as I hosting this for many users.

Portals have their purpose.... not in this instance.

Copy and Paste.... it does work, and will NOT conflict down the line considering I am using ID #'s.

Let me rephrase my question....

My contact databse contains all info about my contact. My job database contains all info about the actual job, and limited info on the contact. It would be nice to jump from my job database to my contact datbase without a "copy and paste" script. I am searching for another value, script or calculation to jump databases, but maintain my "active" relationships.

I have databases for contacts, jobs, purchase orders, faxing, scheduling, shipping, etc. (many others) I would say the heart of ALL these databases centers on the contact, logically. I want to meuever between databases easily, maintaining the critcal relationship.

Again, thank you!

Any suggestions.... yes, copy and paste is "primative" but does actually work.... I am looking for another solution.

[macmangler]

OK.... going to some serious research and playing with portals now. Maybe you're correct... portals are just new to me.

I jumped to FM8 and have so many new toys since 6...

[Razumovsky]

Also.... everyhere I have ever read suggests to keep separate databases for every possible calculation,

You should do some more reading. That is quite an extreme stance and not at all the standard from my experience. In fact, there are many people trumpeting the exact opposite. I suspect most people find a happy medium. Plus, I have not seen any evidence that a Single file solution is in any significant way slower than a multiple related file one over a network. I have seen evidence that a single file solution does open up faster over a network, however.

Copy/paste are largely disparaged for good reason:

1. The require that the field to be Copy or pasted from/to be present and accessible on the current layout.

2. They clear the contents of the clipboard (leaving users wondering what happened to that JPEG or Text they thought they had on there...)

3. They require at least 2 steps to transfer data from one field to another.

As Brian C said, the set field step can accomplish nearly all the same tasks much more efficiently while avoiding these three issues.

Have fun with the portals!

[macmangler]

I just digested your post.... multiple tables in one database! I spent the last 24 hours away from Filemaker... my head was about to explode... especially because I outlined my database with FM6, and upgraded to FM8... with all these new tools and toys... I have to rethink everything.

So... again.... multiple table in one database!!

I have a whole new vision now! Thanks for waking me up!