Copy Content of Portal to Next Record

[Spirodoc]

I have a file that relates customers to products. Products ordered show on the customer table in a portal. Is there a way to copy a customers product list from Day#1 to Day#2, since customers often repeat their product choices. Copy the portal contents of Day#1 to Day#2? Any help is appreciated!

[Søren Dyhr]

Since you're on Fm7, is the best way to solve this by a recursive script:

If [ IsEmpty ( Get ( ScriptParameter ) ) ] 

        #Make the portals content a found set 

        Go to Related Record [ From table: “portal”; Using layout: “portal” (portal) ] [ Show only related records ] 

        #Create a new main table record 

        Go to Layout [ “Main” (Main) ] 

        New Record/Request 

        #Call the script itself recursively 

        Perform Script [ “New Script”; Parameter: Main::RecordID ] 

        #The end of all scripting 

        Go to Layout [ original layout ] 

Else 

        Go to Layout [ “portal” (portal) ] 

        Go to Record/Request/Page [ First ] 

        Loop 

                Duplicate Record/Request 

                #The dupe goes to end of the found set 

                Set Field [ portal::ForeignKey; Get ( ScriptParameter ) ] 

                #This record is new so it shouldn't be duped any more 

                Omit Record 

                Go to Record/Request/Page [ First ] 

                #This record has been duped, lets get rid of it in the found set 

                Omit Record 

                #When all have been duped is the found set empty, lets get back to where the recursion was called 

                Exit Loop If [  not Get ( FoundCount ) ] 

       End Loop 

End If 

I then attach a template with an uncommented version of the same script:

--sd

portalDupe.zip

[MattFM]

Hi Soren... Found this solution of yours and it looks great..

I am trying to do a similar thing, but use FM9 and was wondering if you had an easier/better way?

[Søren Dyhr]

Oh yes it can indeed be tidied by the use of $variables, something else is if each of the records has an ID in the portal's table, and this is something both templates actually can benefit from - take a look you then sort found set reversely before duping as well as starting in other end of the set.

Go to Related Record [ From table: “portal”; Using layout: “portal” (portal) ] [ Show only related records ] 

Go to Layout [ “Main” (Main) ] 

New Record/Request 

Set Variable [ $newForeign; Value:Main::RecordID ] 

Go to Layout [ “portal” (portal) ] 

Sort Records [ Specified Sort Order: portal::recordID; descending ] [ Restore; No dialog ] 

Go to Record/Request/Page [ Last ] 

Loop 

      Duplicate Record/Request 

      Set Field [ portal::ForeignKey; $newForeign ] 

      Omit Record 

      Omit Record 

      Exit Loop If [  not Get ( FoundCount ) ] 

End Loop 

Go to Layout [ original layout ] 

--sd

portalDupeNine.zip

[Spirodoc]

Soren,

Thanks for the information and the duplication works exactly as I'd hoped. I too have upgraded to FM9 so I have used your most recent reply to my initial post.

Thanks Again!

Spirodoc

[Spirodoc]

My portal will display on my layout, however when I print the layout or go to Preview mode it does not show. Shows perfectly in layout mode. Any help in understanding this will be appreciated.

Thanks!

Spirodoc

[Søren Dyhr]

Portals are not designed for printouts, instead make a new layout in lineitems table. When the urge occures issue a GTRR(SO) ... this bring you to a found set of the portals records. In the header/footer area bring in the related fields ....

Well - see if you can get the gist of it by studying this:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000717

If you should need some kind of sliding, might this be a good inspiration:

http://fmcollective.com/2007/08/29/pseudoportals-with-alternating-fill/

--sd

[SamMc]

Hello:

This is what I just needed for my DB.

I have some questions:

If I use this script, will the portal records in my new table properly "related" in the new record?

[Saubs]

I have a question about this, as it applies to a solution I'm currently building.

Soren, your script works great, thank you for sharing it with us. My situation is a bit more complicated in that I only want to duplicate the related records under certain conditions. I hope I can explain this well:

If the data in the 1st portal row of the 2nd record doesn't match the data in the 1st portal row of the 1st record, or if there is no data in the 1st portal row of the 2nd record, then go ahead and copy the portal data from record 1 to 2 as described in Soren's script.

On the other hand...

If the data in the 1st portal row of the 2nd record DOES match what's in the 1st portal row of the 1st record, then I don't want to duplicate the related records as described in Soren's script or else the result will be two portal rows in one record showing the same data.

Same thing goes for portal rows 2, 3, 4, and 5. Let me replace the abstractions with some examples:

Record one's portal is showing this:

CAT

DOG

MOUSE

COW

Record two's portal is showing this:

MOUSE

DOG

AARDVARK

When the user clicks an UPDATE RECORDS button in record one, I want the script to make the following changes to record two's portal:

-Replace MOUSE with CAT

-Don't replace DOG with DOG

-Replace AARDVARK with MOUSE

-Add COW into the 4th portal row.

Of course there will usually be more than two records in the found set. I'm thinking the 'main' script could just loop through the found set and call a subscript until Get(RecordNumber)=Get(FoundCount)

I've spent an hour trying to figure this out and I'm stumped. Any suggestions greatly appreciated. Thanks, all--

[Robert Collins]

I came across this post when looking for a solution to a very similar problem - I have used you template to show what I am trying to achieve. I need the button you created to create a new record , but I have a mark box next to each field and it is only these marked portals I would like transferred to the new record

Any help would be appreciated as I have spent many hours trying to come up with a solution (including silly copy / paste commands)

thanks

[Robert Collins]

sorry , this the right file

[Søren Dyhr]

First off do I apologize for not having seen these additional questions, I will in the following postings address each of those.

...only it will be tomorrow!

--sd

[Søren Dyhr]

Here's the answer for Robert Collins:

Go to Related Record [ From table: “portal”; Using layout: “portal” (portal) ] [ Show only related records ] 

Enter Find Mode [  ] 

Set Field [ portal::Field1; "m" ] 

Constrain Found Set [  ] 

Go to Layout [ “Main” (Main) ] 

New Record/Request 

Set Variable [ $newForeign; Value:Main::RecordID ] 

Go to Layout [ “portal” (portal) ] 

Sort Records [ Specified Sort Order: portal::recordID; descending ] [ Restore; No dialog ] 

Go to Record/Request/Page [ Last ] 

Loop 

     Duplicate Record/Request 

     Set Field [ portal::ForeignKey; $newForeign ] 

     Omit Record 

     Omit Record 

     Exit Loop If [  not Get ( FoundCount ) ] 

End Loop 

Go to Layout [ original layout ] 

But turn field1 into textmode!

--sd

[Søren Dyhr]

will the portal records in my new table properly "related" in the new record?

Yes, take a look ... I first gather the portal-row records in a found set and then turn to the main table and creates a new record from where I get the new foreign key for the newly generated records.

--sd

[Søren Dyhr]

I've spent an hour trying to figure this out and I'm stumped.

So would I be, by your description. One very important ingredient missing is how many fields are each portal row's record are likely to hold. If this is just one single field only exist would it make sense to delete all portal rows in the recieving set.

I do however notice you're on a mac, so applescript might be an option, utilizing it would require a layout consisting of only the relevant fields for this action and this:

http://tokerud.typepad.com/filemaker/2006/06/new_record_with.html

...using the newly created object in the referencing.

However if the data wich should be replaced only is one single field among many in the portal could it get a little bit more tricky. What I miss here is how the two main records are selected both the sending and the recieving record?

--sd

[Robert Collins]

thanks very much , that worked great.

I need to add a command to paste the current date in the old record. I think I know the commands, i'm just not sure where in the script they need to be

Insert Current Date[select; portal::Date sold

Set Field [portal::Mark item field ; ""]

thanks again

[Søren Dyhr]

Where you are setting the foreign key in the script - the lines just after!

...and please use:

Setfield[portal::DateSold;Get(CurrentDate)]

SetField[portal::MarkItem;""]

But I would however automate it further, make the mark field autoenter updating when the date sold is modified.

--sd

[Robert Collins]

thanks for the prompt reply - I have managed to get the new records not to be marked , but the old records are still marked and have no 'date sold' . could you please show me in your written example where to put the set field commands. Sorry to be a pain.

thanks

[Søren Dyhr]

Depends on when the records are marked, if it's when the record still doesn't have a sold date, is the solution not normalized enough, since the presence of the date simply is an derivation of the date via IsEmpty(

Take a look at the changes I've made to you template, is it what you mean functionality wise?

But as such isn't it particular normalized to deliberate dupe available records to make them show up in yet another record, when it could be done by adding each of the primary keys pilcrow delimited to each portal rows foreign key field. In short would I hate if you take your initial take on availiability any further along the duping path - it's the wrong approach to use!

--sd

DupmarkedonlySD.zip

[Robert Collins]

Thanks for your help on this - I am nearly there.

Instead of duplicating the entire portal record(s) I would just like one field to be moved across so I thought replacing the following lines in the Loop

duplicate record/request

with

New record/request

Insert from last visited ; copy_field

but it does not seem to work

[Søren Dyhr]

Either you duplicate or create a new record, not both:

Go to Related Record [ From table: “portal”; Using layout: “portal” (portal) ] [ Show only related records ] 

Enter Find Mode [  ] 

Set Field [ portal::Date sold; "*" ] 

Omit Record 

Constrain Found Set [  ] 

Go to Layout [ “Main” (Main) ] 

New Record/Request 

Set Variable [ $newForeign; Value:Main::RecordID ] 

Go to Layout [ “portal” (portal) ] 

Sort Records [ Specified Sort Order: portal::recordID; descending ] [ Restore; No dialog ] 

Go to Record/Request/Page [ Last ] 

Loop 

     Set Variable [ $oldData; Value:portal::Telephone number ] 

     New Record/Request 

     Set Field [ portal::ForeignKey; $newForeign ] 

     Set Field [ portal::Telephone number; $oldData ] 

     Omit Record 

     Omit Record 

     Exit Loop If [  not Get ( FoundCount ) ] 

End Loop 

Go to Layout [ original layout ] 

...please note you use the telephone field for product!

But I'm still not completely in love with the evasiveness you exhibit with the relational matters. This line of scripting produces tons of redundant data ... which is conflicting with the foundations of database development.

--sd

Edited March 18, 2008 by Guest

[Robert Collins]

Thanks , that worked fine.

[Søren Dyhr]

But you're not taking the cautioning serious??

--sd

[Robert Collins]

S.D wrote:

"This line of scripting produces tons of redundant data ... which is conflicting with the foundations of database development."

How can copying one fields contents produce 'tons of redundant data' ?

With all due respect , you have not seen the context in which your help has solved my problem (for which I am very grateful)

Maybe by using the example file, it did not fully indicate the application for this solution but at the end of the day it achieved its goal.

Thanks again

[Søren Dyhr]

you have not seen the context in which your help has solved my problem (for which I am very grateful)

For not revealing the context, what a strange kind of gratitude ... It's exactly the line of questioning we all fear here. We're anxious not to let people get away with the wrong way of solving a problem, which perhaps might nail your present problem but indeed would be teaching you bad habits, if it in any way gets generalized.

When ever copying data is it bound to reflect a historic presentation of a measure, say an instance of a price level for a given item on a certain day in an enviroment where rapid changes occures.

Relational theory on the other hand tries to solve issues of synchronization, by storing every atomic data in just one place - and make use of it by referencing to it's location.

The deliberate denormalizations, your problem seems to exhibit should then be argued on behalf of since the normalization should be the obvious first step and then isn't there any "in dubio pro reo" pending, so if the action is deliberate would the answer be straight forward.

How can copying one fields contents produce 'tons of redundant data' ?

Only keyfields could appear redundant in a solution, not data!

--sd

[Robert Collins]

I think you misunderstood me:

I meant I am very grateful for solving my problem , not grateful that I have not shown you the context!

[Søren Dyhr]

Well I planted the wrong interpretation by purpose to make you think...

--sd