Using the Database Tool to transfer data from a Record Format to another Record

[DGMacNair]

Can data be transferred to a custom record format automatically? I am trying to use the Manage Database tool to move data from a record (valve or hydrozone) to another record created using a new record format tied to a database. This approach is challenging and I have not had success. The goal is to be able to use the collected data in a worksheet for various calculations. I have tried manipulating worksheets but it seem the object types don’t work together. Presently I manually fill in the info from the object record  into a custom record. Very tedious and time consuming. Any collective thoughts?

[Pat Stanford]

Post a file with a couple of the object types and what you want the report to look like.

 

This can probably be done more easily in a worksheet with the right criteria and some IF statements rather than trying to automagically move data between record formats.

[DGMacNair]

Thank you Pat. File attached. I have trying to do this for a long time without success. It would be great to automate if possible.

 

 

Worsheet Test.vwx

[Pat Stanford]

OK. I think this does what you want.  It was more complicated than originally explained because some of the Hydrozones actually have multiple areas that have to be summed together.

 

Copy everything thing in the code block below and paste it into a new blank Vectorscript editor window named "SumField2FromField1Match"

 

In the Database header row enter a formula of:

 

=RUNSCRIPT('SumField2ForField1Match', 'IrrigationHydroZone', 'Hydrozone Name', 'Area', B17)

 

Replace the B17 with the cell in the header row that contains the value of the IrrigationZone.Name value. This will be the link between the two records.

 

Click Yes when asked if you want to run scripts. I recommend the Yes Always or you will have to click yes every time you recalculate the worksheet.

 

Note that due to rounding differences, there may be a difference in the last digit of the number returned versus what is shown in the IrrigationZone database.

 

If this works and helps, then you can store this script in a Script Palette in each document. If you are going to use this often, I can also show you have to store the script and change the RunScript formula to have the script in your User Folder or if multiple people need the script in a Workgroup Folder.

 

Let me know if you have more questions.

 

Regards,

Pat

 

ps. @Sam Jones This is a pretty good example of worksheet scripts and is the one we based out conversation on Saturday on.

 

Procedure SumField2ForField1Match;

{May 22, 2022}
{October 3, 2020}
{©2020, 2022 Patrick Stanford pat@coviana.com}
{Licensed under the GNU Lesser General Public License}

{No Warranty Expressed of Implied. Use at your own risk.}

{Based on a previous sample script to show how to use the DatabaseByScript}
{functionality in a worksheet to create your own objects when}
{the standard criteria are not enough.}

{This script 

{This script is run from the Database Formula Bar using a syntax of:}
{=DatabaseByScript('SumField2ForField1Match', TheRecord, Field1, Field2, MatchValue}
{Where TheRecord, Field1, Field2, and Match value are strings contained}
{inside of single quotes. }
{TheRecord is the name of the Record that has both Field1 and Field2}
{Field1 is the name of the Field containing the data that could match MatchValue.}
{Field2 is the name of the Field containing the value }
{to be summed and returned by the script.}

{This script finds all of the objects in the document that have a value}
{of TheRecord.Field1 that matches the value of MatchValue.}
{It returns a Real (number) that is the sum of the values stored}
{in TheRecord.Field2 for those objects. If the value in Field2}
{can't be converted to a number by removing suffix characters}
{the conversion will be marked as Failure and a value of 'No Number'}
{will be returned instead of a summed value.}

{Procedure Execute does the heavy lifting. It attempts to convert the string}
{representation of ReturnField into a Real. If the conversion fails}
{it drops the last character and tries again.

{The main body of the code only gets the parameters that are}
{passed to the script and uses the passed Record & Field}
{as Criteria in the ForEachObject procedure. ForEachObject}
{gets a Handle for each object that matches the criteria}
{and passes the handle to that object to the Execute procedure.}
{As written, the criteria is that the object has the}
{value of TheRecord.Field1=Matchvalue.}

{The only error checking in this script is that the data in}
{the field Field2 actually converts to a valid number.}

{Do not operate heavy machinery or drive an automobile}
{while using this script. If use causes excessive itching or }
{unexplainable hair loss, discontinue use immediately and see}
{a programmer immediately.}


CONST	SQ=Chr(39);

Var	TheRecord, LookupField, ReturnField, MatchValue, TheCriteria: String;
	StrToConvert:String;
	SumOfReals, ConvertedToReal: Real;
{	N1, N2: Integer;}
	B1, Failure:	Boolean;

Procedure Execute(Hd1:Handle);
{Converts the value in TheRecord.TheField combination to a number}
{If it is a valid number then it compares the data to CMin and CMax}
{If the value is in the desired range, then add the object specified}
{by Hd1 to the database}

	Begin
{		S1:=GetRField(Hd1,TheRecord,LookupField);}
		ConvertedToReal:=0;
		StrToConvert:=GetRField(Hd1,TheRecord,ReturnField);
		B1:=ValidNumStr(StrToConvert, ConvertedToReal);
		While ((B1=False) And (Len(StrToConvert)>1)) DO
			BEGIN
				StrToConvert:=Copy(StrToConvert, 1, Len(StrToConvert)-1);
				B1:=ValidNumStr(StrToConvert, ConvertedToReal);
			End;
		If B1=False then Failure:=True;		    		{Error checking for conversion error}
		SumOfReals:=SumOfReals+ConvertedToReal			{Sum up the converted values}
	End;
	
Begin
{Get the parameters passed top the script}
	SumOfReals:=0;
	Failure:=False;
	TheRecord:=WSScript_GetPrmStr(0);
	LookupField:=WSScript_GetPrmStr(1);
	ReturnField:=WSScript_GetPrmStr(2);
	MatchValue:=WSScript_GetPrmStr(3);

{Create the proper criteria string from the passed Record, Field, and Match Values}
	TheCriteria:=Concat('((',SQ,TheRecord,SQ,'.',SQ,LookupField,SQ,'=',SQ,MatchValue,SQ,'))');
{For debugging purposes uncomment the following line to view the TheCriteria. This will require}
{user to hit return for every subrow of the database when uncommented.}
{AlrtDialog(TheCriteria);}

{Use the criteria to run Procedure Execute based on the Criteria}
	ForEachObject(Execute, TheCriteria);
	
{Return the value of the sum of the Field ReturnField for all items matching TheCriteria as a Real}	
{or a string of 'No Number' if there is a conversion error}
	If Failure=False then WSScript_SetResReal(SumOfReals) else WSScript_SetResStr('No Number');
End;

Run(SumField2ForField1Match);

 

[DGMacNair]

Hi Pat, 

I did not realize until yesterday you provided this script. Thank you and my apologies for not getting back to you sooner! I have tried to get this to work for me but without success. I am not well versed in scripting so I may be doing something inaccurately. The result I get is always zeros. From the debugger there seems to an error in the script and it's beyond me. Were you able to get it to work? Other than driving me batty, there's no urgency. If you have a moment to check it out that would be awesome. I have included the file I have been working with, let me know if you need any additional info.

Thanks again for your time!

 

 

Don

Worksheet Same Value Test.vwx