Duplicate based on variable

[Dominic Filteau]

Hi, i'm wondering if there is a better way to duplicate a record based on a quantity field.

i have it working in the following way:

if quantity = 2

duplicate record

end if

if quantity = 3

duplicate record

duplicate record

end if

and so on..



But my wet dream would be to have something like 

Set variable $DupNum To field quantity

loop $DupNum times

duplicate record

end loop

i could do it in applescript, but it will be run on PC too.

Any Scriptmaker way to do so?

[Søren Dyhr]

Hows about:

Omit Record 

Show Omitted Only 

If [ numberOfTimes ] 

     Loop 

          Exit Loop If [ numberOfTimes = Status ( FoundCount ) ] 

          Duplicate Record/Request 

     End Loop 

End If 

--sd

[Dominic Filteau]

Thanks for the answer,

this will work for 1 record at the time, but I didn't mention I often have to apply this to multiple records in one run.

my current way of applying it is

script 1:

activates first record

loop

 run duplicate script (see 1st post)

activate next record, end after last

end loop

Edited January 22, 2008 by Guest

[Søren Dyhr]

Ah you are on fm9, that makes it whole lot different both scriptparameters and recursive scripting is posible - but as such must I say - are you absolutely sure it's a good idea to make redundant data.

Before we progress do we need to know context and purpose, to prevent solving the wrong problem, could it be that the entire duplication business were better off with a return delimited list for for the keying purpose. Or that non-equi joins might solve this better??

--sd

[Dominic Filteau]

it's a staff booking system that was made under FMP6 that i'm transfering to 9,

in one DB, people are putting their needs, 2 repeating fields,one is the number, the other the function ex:

1 carpenter

3 painters

2 welders.

then the data is imported in another BD wich serves as "ongoing calls"

upon import, each line of the repeating field is imported, but to put a name on each line, i must duplicate records matching the number of people asked for the job asked.

So on import, based on the above example, i'll have 3 records

Num / function / name(in 2nd db)

1    carpenter    empty

3    painters     empty

2    welders.     empty

NUM is just there as reference for the duplication



needs to become

1    carpenter    empty

3    painters     empty

3    painters     empty

3    painters     empty

2    welders.     empty

2    welders.     empty

then i'll put the names via another DB list, based on speciality..

Hope it makes sense!

[David Jondreau]

What you want to accomplish relatively simple using a variable the acts as a counter and a couple Loops, one nested inside the other. But we here at FM Forums like to go the extra mile (to some people's pain and others' pleasure) and as Soren points out, the need for this seems fishy.

Just read your new post though, and while the use of a repeating field is unnecessary (why not use two fields?) and the import is confusing (why not just create new records?) Needing those extra records make sense.

If you choose not to change anything, this would probably do the job.

Set Variable[$end1; Get(FoundCount)]

Set Variable[$counter1; 1)

#Sorting will ensure dupicates are placed after

#original and not at end of Found Set

Sort Records [No dialog; PrimaryKey field]

Go To Record [First]

#Looping through Found Set

Loop

If[Quantity]

Set Variable[$counter2; 1]

Set Variable[$end2; Quantity]

#Looping through duplicating this record

Loop

Duplicate Record

Exit Loop If[$counter2=$end2]

Set Variable[$counter2; $counter2+1]

End Loop

End If

#Check to see if original found set has been duplicated

#If not increment counter and go to next record.

Exit Loop If[$counter1=$end1]

Go To Record [Next]

Set Variable[$counter1; $counter1+1]

End Loop

Edited January 22, 2008 by Guest

[Dominic Filteau]

Set Variable[$counter2; $counter2+1]

Thanks for adding this one... i just figured it out :

i've placed it right after the duplicate tho..

Edited January 22, 2008 by Guest

[Søren Dyhr]

then the data is imported in another BD wich serves as "ongoing calls"

upon import, each line of the repeating field is imported, but to put a name on each line, i must duplicate records matching the number of people asked for the job asked.

This is definitely wrong, a record can have all the relational foreign-key you might wish to, linking them to other tables, and by that make them visible from that point of view.

The demands should be put as portal-rows, certainly not repeaters, so the making them shown in another table is just looping thru the portal records providing them with the key value to make it appeare in the other tables portal view.

--sd

[Dominic Filteau]

I have added a line there;

#Looping through duplicating this record

Loop

Duplicate Record

Exit Loop If[Quantity=1]

Exit Loop If[$counter2=$end2]

Set Variable[$counter2; $counter2+1]

End Loop

[Dominic Filteau]

Thanks for the tips,

works fine now with the embedded loops

SD: thanks too,

i'm not starting from scratch, so that is my major problem,

the old version 6 db already weight 100MB combined, so i'm trying to get the new version work more like a "by module" since it's hosted via the internet, i,m trying to have only required modules to retreive data when needed.

the whole system magages quotes, labor calls, paychecks, customers, tax deductions, and all finished/paid jobs are moved to another DB wich serves for archiving.

works great as is right now, but the person who programmed it in the first place made it so complicated to update... just changing hourly rates was a pain..had to go in 3-4 DBS before..now everything active relies on one DB, and the archives are just raw data..

You might see me asking some more fun stuff soon :

[Søren Dyhr]

the old version 6 db already weight 100MB combined

It's obvious the institutionalized redundancy is going to make whopping filesizes.

--sd