Creating a button that copies data from one table record to another.

[MutantST]

Hi All,

I would like to create a button that copies data from specific table and fields to other ones on display in my form. The context for this is that I am creating a button that copies data that has already been entered and displayed in some text merge fields. i.e., Account address (first table), and I would love to have that data copied to the location address fields (second table).

See attached file.

Any help would be gratefully received (Complete beginner).

 

Cheers, Simon

DB_SC_Grab- button question.tiff

[Rick Whitelaw]

What have the data in two different locations? It seems unnecessary in a relational database.

[MutantST]

Yes, although It may only occasionally happen that the location of the photo shoot is the same as the Account  location details, but I want a button to quickly add this information. 

[webko]

Assumption - the layout shown uses related fields to display the information at top right - let's say the relationship is called Shoot_Account, so Contact Name is really  Shoot_Account::ContactName

A script could set the local (Shoot fields) like:

Set Field [Shoot::LocationBusinessName; Shoot_Account::LocationBusinessName]
Set Field [Shoot::FBN; Shoot_Account::FBN]
etc

[MutantST]

I have just entered  . .. 

Set Field [ShootLocations::LocationBusinessName; GeneralAccounts::GeneralAccName]
Set Field [ShootLocations::LocationFBN; GeneralAccounts::GeneralAccPhisAddressFBN]
etc..

and it returns . . .

Specified table cannot be found.

Your assumptions were correct though

[webko]

It's got be a related field - can you screenshot the layout in layout mode please?

 

[MutantST]

Attached

layout 1.pdf

[webko]

No - on the layout that you originally sent, go into Layout mode so we can see the field names...

[comment]

1 hour ago, MutantST said:

Set Field [ShootLocations::LocationBusinessName; GeneralAccounts::GeneralAccName]
Set Field [ShootLocations::LocationFBN; GeneralAccounts::GeneralAccPhisAddressFBN]
etc..

and it returns . . .

Specified table cannot be found.

You need to use your own field and table names. If your parent table is named Accounts and your child table is named Bookings, then use:

Set Field [ Bookings::Somefield ; Accounts::Somefield ]

to copy the information from one field in the parent table to a corresponding field in the child table. Hopefully you have the account address broken up into the same fields as required in the Bookings table (I don't know how to read "data that has already been entered and displayed in some text merge fields").

This is a data-level operation, so screenshots depicting layouts are largely irrelevant. A screenshot of your relationships graph, showing how the two tables are related and what fields each one has, would be much more useful.

 

Edited January 19, 2016 by comment

[MutantST]

19 hours ago, comment said:

You need to use your own field and table names.

Those are my field and table names. For the relationship graph please see my previous comment.

Cheers

[comment]

6 hours ago, MutantST said:

For the relationship graph please see my previous comment.

Yeah well, if you name your file "layout 1.pdf" then I don't expect to find the relationships graph in it...

Anyway, those are not your field and table names. Your child table is named Bookings, not ShootLocations - so your script needs to read:

Set Field [ Bookings::Somefield ; GeneralAccounts::Somefield ]

Unfortunately, your screenshot does not show the relevant field names, so replace "Somefield" accordingly.

Note that you must be on a layout of Bookings (and not, for example, on a layout of Bookings 2) for this to work.

Edited January 20, 2016 by comment

[MutantST]

Apologies for not naming the file incorrectly.

Apologies in fact for seeming real dim, but some of the information I would like moved (using a scrip) is here: GeneralAccounts::GeneralAccName and needs to be copied and pasted here: ShootLocations::LocationBusinessName (to keep it simple I will just move this one piece of info first). I have created a new relationship graph (attached) with all of my tables in it with all fields in view. I honestly do not know what I am missing as the tables and fields seem quite obvious.

Relationships Graph 200116.tiff

[comment]

57 minutes ago, MutantST said:

GeneralAccounts::GeneralAccName and needs to be copied and pasted here: ShootLocations::LocationBusinessName

For this, you need your script to do:

Set Field [ ShootLocations::LocationBusinessName ; GeneralAccounts::GeneralAccName ]

and you must call it while you are on a layout set to show records from ShootLocations.

 

I am not sure why this is so difficult for you. Perhaps you're not familiar enough with the Set Field[] script step. You need to select ShootLocations::LocationBusinessName as the target field, and specify GeneralAccounts::GeneralAccName as the calculated result.

 

 

 

 

[MutantST]

So would you agree that the script you provided is the same as the first step in the script posted in my third post, but the only difference being that it should be actioned on the shoot locations layout?

[MutantST]

Thanks for all your help everyone. I have managed to find a workaround that may seem a bit tedious to you all, but has been a great learning experience for me (I am sure there will be many more). Here is my script attached (Sorry, couldnt copy and paste it in here for some reason. Let me know how to do it if you know, ready for next time )

 

Moving stuff around .tiff

[comment]

44 minutes ago, MutantST said:

I have managed to find a workaround that may seem a bit tedious to you all

The problem with your workaround is not (only) being tedious, but unreliable. You assume that when you go to the Accounts layout, you will land on the parent account record of the current record in Bookings. There is no basis for such assumption. To do it in such a roundabout way, you should use the Go to Related Record step to make sure you navigate to the correct record. But of course, since the correct record is identified through a relationship, you can use the same relationship to fetch data directly from it, without having to actually go to it - as was suggested originally.

BTW, you should not use global $$variables, unless you want them to persist after the script has run out.

 

54 minutes ago, MutantST said:

Sorry, couldnt copy and paste it in here for some reason.

Next time, please do a print preview of your script and copy/paste from there. Also, if posting images, make them PNG or GIF or JPEG.

[MutantST]

14 hours ago, comment said:

Next time, please do a print preview of your script and copy/paste from there. Also, if posting images, make them PNG or GIF or JPEG.

Thanks for that, will do.

So, I had a second look at the original script that was suggested and which I entered at the time. I am not sure what has changed, but it does exactly what it said on the tin. Anyhow, again I thank you for your time and patience with me