duplicating one record for a set of related ones

[alin]

I have a database for a merchandising company, converted from FM 6, so, it still has multiple files. The main one "mission" is related with many others like "employee", for the employee doing a certain job, "clients" for the client who ordered the job, "shops" for the point of sale where the assignment takes place, even "invoices" to automatically obtain the invoice monthly for each client.

My problem now, is to find a way to save sets of shops and be able to duplicate one mission for each of those shops from my saved set. The user's request appeared from the need to avoid a lot of manual work, because there are tens, sometimes hundreds of similar tasks, in behalf of the same client, having the same date, and all the other characteristics the same, but a different place, which is the point of sale where they are carried out.

Any help any of you could give would be verry much appreciated.

[alin]

I don't know if I made myself clear, I hope I did. Anyway, if any of you has any idea, but doesn't understand exactly what I meant, I will try to explain it again, more understandable.

I'm kind of desperate, because I'm supposed to do the modification this weekend, while they are not using with the application, but I don't know how to do it, not even if it is possible the way I put it here.

Any alternative way is welcome, too.

Thank you in advance!

[QuinTech]

Hi, alin. My first thought would be to create several scripts that created related records in the Mission file. One of these could create a mission and populate the fields with the most commonly-used values, which users could then edit. So, for example, you'd have a script called, say, "OS Upgrade". This would set Department="IS", Client="City University", Hours="1.5", Cost="$50", Notes="Upgrade from W2K to XP", etc.

Is this what you're looking for?

HTH,

Jerry

[alin]

Thanks QuinTech for your quick answer!

Yes and no. It would be a good idea to do it using scripts, from the related file to create missions, but this doesn't actually give me the solution. Let me try to explain it again on your example...

I will add one more thing, the City University wants to do the same OS upgrade in all the computer rooms. Let's say a number of one hundred. Different locations, more than one campus, all over the town. Same date. Only the room is diferrent, everything else stays the same (client = "city university", duration = "1.5" hours, cost = "50")

...and the employee assigned to do the job will be different, one for each computer room, but this will be changed/added manually later on.

In this way the shops, from my example, become the computer rooms from yours, meaning, in fact, the physical location of the job, every and each one has a location.

Now, the question is, can I do a script in the table with the locations (computer rooms/shops) to create 100 missions in the mission file, one for each location(computer room/shop) for a found set in the location file?

And how will it look like that script?

The second question would be if I could save that set of locations for further use, because, i.e. in one month time I could have another assignment "Applying security patches for the newly installed XP!"

Thank you again

Alin

[QuinTech]

I see. So, let me start with some assumptions (tell me if any of these are impossible):

You have 100 records in the Location table (or, at least, in the found set).

You are on a layout that includes related fields from the Mission table.

The relationship from Location to Mission allows you to create related records in Mission.

OK, given those assumptions, i think you can do question 1 with a simple looping script:

Loop

 Set Field (All the fields you want blah blah blah)

 Go To Record (Next, Exit After Last)

End Loop

Does that answer the first question?

As for the second question, i think you are asking how to save a found set to be re-used in a script. If that's the case, search the Scripts forum for "+save +found +set" (without the quotes); you should find a wealth of information there, i imagine.

J

[alin]

Once again,thank you, QuinTech!

The assumptions are correct, all of them!

...and it should work, it seems logical, too. But I'm STUPID , I cannot get it working myself, it looks like i'm missing something, but I don't know what that is...

I attached an example file to the post.

It was working the first time when I created the missions, after that it was only rewriting the first created missions.

I've tried to modify the looping script and I added to it a step to a script from the mission file, which is creating the new record to be changed by the looping script. Doesn't work either, the result is that I'm only creating empty missions, now.

If you can tell me what I'm missing I'll be very grateful.

Another thing: is it possible to make the script ask for the user input for those values, for the fields we are setting? and of course, only at the beginning not at each loop.

Alin

test.zip

[QuinTech]

OK, i used a little bit of sloppy shorthand logic. Sorry about that. I'll answer your last question first because it's easier:

is it possible to make the script ask for the user input for those values

Two ways you can do this: (1) Use the Show Custom Dialog script step to ask for input. I would find that harder than (2) create a bunch of globals (globalProduct, globalClient, ...), have the user input desired values into those, then trigger the script that sets the related fields to the values of those globals.

OK, for the bigger question: You will need to do this script through a portal, which is an important part that i left out. The way you are doing it will just reset the value of the first related record; to get at a second related record in Missions, you will need a portal. Take a look at the attached for an example of what i mean.

Also, could you please update your profile to reflect that you are using FM version 6 (if that is, in fact, what you generally use)? It's a convenient shorthand for us. Thanks!

J

testReturned.zip

[alin]

Thanks a lot, QuinTech!

Now it makes sense and besides this it is working, too. Including the globals used to ask the user for the desired values. It means that I'm back in business if I can work out the other part, about saving sets of shops.

Actually, I'm using FMP 7, but I did the example in 5.5 because I had seen you are using Developer 6. I hope it makes no difference...

It's true, I have problems in FMP 7 with an example of J.M Osborne about saving found sets, but I think it suits best, the ScriptMaker section of the forums, I'll post it there.

What if I have two portals on my (shops) layout, the layout from where I launch the creation of the new missions? I've seen no way to tell FMP (5.5 or seven) in the script which portal it should go to...

In the end, I feel the need to mention that you are the only guys I know using Filemaker and, in the same time you are great, the best FileMaker resource! And I'm sure I'm not the only one thinking this way...

Would it be useful for others searching the forums to post the final files, the ones using globals?

Thanks again, and have a nice weekend!

[alin]

Hello, again...

I'm not so happy anymore... when everything seemed fine I've noticed that the script supposed to create new missions it is in fact rewriting old ones ...and sometimes adding some new ones. Any idea why?

To be more specific, this happens only in the main database, not in the example posted here... And everything is the same!

Thank you in advance!

[alin]

well, not entirely the same, though...

I have a second portal on the layout from where I trigger the duplication.

Apparently, this is the reason - I removed the second portal from the layout and everything works fine, again.

The problem is that they stubbornly insist in having everything on the same layout. So, is it any way to change the active portal (which, normally, is the other one, when I launch the script)?

This is what I've found regarding the "Go to Portal Row script step":

"Navigates among the rows in the active portal. When no portal is active, the script uses the first portal in the layout stacking order."

Unfortunately, this is also where it ends for me, because I have no idea how to swap the portal's stack order in the layout, or even if it is possible to do this.

Thanks again, and please excuse me for annoying you...

[Fenton]

The solution is simple, but it's a little non-intuitive. Go to a field IN the portal first, then go to the portal row you want.

An alternative (which I generally prefer) is to go to the portal's related records in the related table and process them there, as records. That way you know where you are; just make sure the layout you specify can show the records.

Another technique you might consider, if there are "hundreds" of whatever, and you're duplicating BY moving to another table, is to Import them. Much faster. You must be careful to ONLY import the found set (or you can end up with thousands; don't ask how I know).

Otherwise duplicate with a Loop. You know that when you duplicate in an UNSORTED found set, the new record ends up at the end. Whereas in a SORTED found set it's the next record (which could also result from a sorted relationship). Either way works, as long as you know.