Search Results for 'script'

[Sam Moffatt]

I’d pivot from using a table field and instead use a link to form field with the records in a different form. Then you can create saved searches to filter content and also use the MCLV to get summary information as well. You could use scripting to copy the data over but I think what might actually work is copy and paste with two fields in the table view.

From the form you have currently a link to form field will look normally but it’ll also mean you can more easily do aggregations and searches on it’s contents using the other views.

Technically under the hood I believe table fields are modelled similar to link to form fields, it’s just not accessible outside of the parent record.

[Sam Moffatt]

This is a bit of a hack but you can use a script field to do the ordering. Create a new script field and then you’ll need to put in something that looks like this:

var keyfield_id = 'fld-id';
var picklist_name = 'Picklist name';

function PicklistID() {
	let key = record.getFieldValue(keyfield_id);
	let picklist = document.getPickListNamed(picklist_name).values;
	for(let offset in picklist) {
		if (picklist[offset].value == key) {
			return offset;
		}
	}
}

PicklistID();

In the script above, edit the first two lines and change the field ID (the fld-id text) and the pick list name (the Picklist name) to be the ID of your Dénomination field and the name of the pick list it uses.

Here is an example I used where I put in the field ID and the pick list was called “Marketplace”:

var keyfield_id = 'fld-fa37906add2942c88bce3b500561c42d';
var picklist_name = 'Marketplace';

function PicklistID() {
	let key = record.getFieldValue(keyfield_id);
	let picklist = document.getPickListNamed(picklist_name).values;
	for(let offset in picklist) {
		if (picklist[offset].value == key) {
			return offset;
		}
	}
}

PicklistID();

That will give you an ID starting from zero I believe in order of the pick list that you can then use as a sort field. It’s not as elegant as having the ability for the text field to use the pick list ordering but it’s something you can do right now.

[Stig Widell]

Ok I entered the ”Kalva” in the Script in form Rekryteringsdjur but I didnt run it. Now when I did I got enclosed error

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

So in the Rekryteringsdjur record, you need to create a new form script called “Kalva” and then run that form script for it to create and link the records together because the script does validation that not only you set the bull but also that the bull with that ID exists if that makes sense.

[Stig Widell]

OK Sam,
I imported the Script in your ScriptManager …tfarc into the document Djur successfully (I hope)
and I copied and pasted your Kalva into Scripts in the form Rekryteringsdjur also successfully (I hope) but I havent tried it yet (too late in the evening). How do I start an entry of a new calf, with a new calf record or with an identified mother record? You discussed both alternatives above.

[Sam Moffatt]

So doing that you start to move into aggregation territory and that’s possible, I covered a version of that use case in an earlier thread on the forum. You can see some of the progression over there in how the scripts evolve and see similarities to your own script. You’d need to change some of the ID’s to match what you’re using but I think you can translate that across. The last one might be the easiest to work with though you have the complication that I think all of your values are inside the table field so you need to do two loops: one for each of the parent records and then a loop inside for the value of the table field. If you modelled the sales as a link to form relationship instead you could probably do that in a single pass but computationally probably not too different.

[Sam Moffatt]

It does seem a tad on the large side though there is a compact database option under preferences for the document. The links shouldn’t be too much and the scripts are generally tiny. If it’s just records, I don’t think it should be that large for 142 records. If you’ve got attachments or images embedded that can grow things. One thing to watch out for is that images in notes fields are stored inefficiently by Apple’s rich text control so if you have a lot of images in notes field internally macOS translates though to TIFF images with a very poor compression scheme.

Ok some tweaks to the form script to get the ID’s to align but this seems to work for the Verkocht table and Aantal subfield:

var firstForm_NaamFieldId = 'fld-f1bb4282fd05432b9d3205004508ee17';
var firstForm_tableFieldId = 'fld-b40eebf010de45f4ad4f2d0815cec963'; // was 'fld-4dde0c4712954541a69b18d602bfcb27'? 
var firstForm_tableField_subFieldId = 'fld-60216b72e69b4a9bab98a23c8a019ea9'; 

var secondForm = document.getFormNamed("Nieuw formulier");
var secondForm_NaamFieldId = 'fld-28cad0a0ea4d4177aecd20e6f63fe470';
var secondForm_NummerFieldId = 'fld-30f5df230f0b44479e53a83df9295e38';

function Create_Summaries() {
	// get all of the records from the current form (should be first form)
	for (let currentRecord of form.getRecords()) {
		// create a new record for it in the second form
		let linkedRecord = secondForm.addNewRecord();

		// copy the name across
		linkedRecord.setFieldValue(secondForm_NaamFieldId, currentRecord.getFieldValue(firstForm_NaamFieldId));

		// create the total value field
		linkedRecord.setFieldValue(secondForm_NummerFieldId, currentRecord.getTotalOfLinkedFieldForField(firstForm_tableFieldId, firstForm_tableField_subFieldId));
	}

	// save all changes
	document.saveAllChanges();
}

Create_Summaries();

Similarly adding a Link to Form from the first to the second form, ticking “Show Inverse Relationship” and hiding it linked it back appropriately:

var firstForm_tableFieldId = 'fld-b40eebf010de45f4ad4f2d0815cec963';
var firstForm_tableField_subFieldId = 'fld-60216b72e69b4a9bab98a23c8a019ea9';
var secondForm_linkFromFormFieldId = 'fld-1950d775d8774c38b39535f97d4c40a2';

function TotalValue() {
	let linkedRecord = record.getFieldValue(secondForm_linkFromFormFieldId);
	return linkedRecord.getTotalOfLinkedFieldForField(firstForm_tableFieldId, firstForm_tableField_subFieldId);
}

TotalValue();

Also attached a copy of the form template with the changes. You should be able to import it and it should update your document. The form script should create entries but the field script will need you to link an entry to get it to work.

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

You need to import the Script Manager TFARC to get two extra forms in your own document and run it locally, it imports my library of scripts automatically. The script I created above references one of those scripts to operate, in particular getRecordFromFormWithKey, which scans for records that have a particular value and returns the first one it finds. This is how I map the ID’s back to make the links work.

[Stig Widell]

I am really grateful to you Sam. I dont have the time tonight (Swedish time) to work with it but I have an initial question:
“I’m going to use one of my scripts to get the ID, easiest way to do this is to import the Script Manager and in the “Repository” form run the “Update Scripts” form script”.
This is not for me to do? You did it when you created the form script, right?

[Rob Naaijkens]

Thnx for your help, Sam!

I removed the links and the scripts because my database was about 400MB with 142 records. Still my form/database is about 180MB witch is big I think.

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

Doing this backwards, don’t mind me…

the Father-ID in the calf record should remain the same even when the mother, the year after, will be linked to another pedigree bull

Right, the calf record should remain unchanged though presumably at some point it either is sold or grows up into one of the other forms. Which actually makes me wonder if ideally everything would be one form with relevant sections but I don’t think TF does self joins well (data model quirk).

How do you mean the calf record should be created? I dont understand that? I dont have all information already before the time of birth of the calf, eg date of birth, sex, stillborn etc.

Well you know what you know about the calf to be, you’re just entering it in immediately once you know some of the details just not all of the details. For me personally I find pre-entering partial data slightly easier because then it’s just a matter of inputting the final data once that comes in. Presumably you know which of your cows are calving so that can be a filter there to limit the possible candidates and you could also sort/group by the pasture they’re in.

“Avelstjur” shouldnt be a text field but a number, my mistake.
Yes, if I change that field to a join field I get the pedigree bulls info over to the cows record as a list with one row with all the info of that bull. But I cant get the ID of the bull over to the father-ID field in the record of the newborn calf when I link that calf record to the mother record. How do I manage that? What do you mean with “creative” join field?

Type is mostly inconsequential for the simpler data fields (text/number) and more necessary for formatting than anything. That said it might mess up some comparisons so always useful to keep it consistent.

Workflow wise I think I’d create the calf record first and then just link in the mother and father records from the calf directly. Or create the calf from the mother record and then just link it to the father at that point in time. That’d obviate the need to script because you could just use the UI to select.

I’m going to use one of my scripts to get the ID, easiest way to do this is to import the Script Manager and in the “Repository” form run the “Update Scripts” form script.

Inside “Rekryteringsdjur” I created a new form script called “Kalva” and then this is it’s implementation:

let PARENT_SCRIPT = "Kalva";
document.getFormNamed("Script Manager").runScriptNamed("getRecordFromFormWithKey");

function Kalva() {
	let rekryteringsdjur_kalvningar_id = 'fld-cdb4431a905f4b1dbaa750b07a63b6f1'; // ID of link to form field to Kalvningar form in the Rekryteringsdjur form.
	let avelstjur_kalvningar_id = 'fld-35f714dc0468447c9b5b9875c8a86545';  // ID of link to form field to Kalvningar form in the Avelstjur form.
	
	let foldse_id = 'fld-8e0cd05ffe8d4603a8abb8ca4694b4ee'; // foldse field ID from Kalvningar form.
	
	let keyFieldId = "fld-0e776009803a4c1f8415e7cbb7919858"; // ID of the Djur-ID field in the Avelstjurar form.
	let keyValue = record.getFieldValue('fld-db52d7055ddd40cf95f50bcf8e2fff3d'); // ID of the Avelstjur field in Rekryteringsdjur form.

	// check if the bull is even set or error.
	if (!keyValue) {
		Utils.alertWithMessage("Fel!", "Avelstjur värde ej inställt!");
		return;
	}

	// look for matching bull ID record; note this assumes the bull ID is unique.
	let avelstjurRecord = getRecordFromFormWithKey("Avelstjurar", keyFieldId, keyValue)
	if (!avelstjurRecord) {
		Utils.alertWithMessage("Varning", "Det gick inte att hitta posten!");
		return;
	}

	// Create a new calf record from the rekryteringsdjur form.
	let kalvningarRecord = record.addNewRecordToField(rekryteringsdjur_kalvningar_id);
	
	// set foaling date to current date.
	kalvningarRecord.setFieldValue(foldse_id, new Date());

	document.saveAllChanges(); // flush out model changes for new record creation.
	
	// add the calf to the bull's record ID.
	avelstjurRecord.addRecordToField(kalvningarRecord, avelstjur_kalvningar_id);
	
	document.saveAllChanges(); // save adding calf to bull

	Utils.alertWithMessage("Gjord!", "");	
}

Kalva();

The first two lines setup a variable some of my scripts use to identify themselves and import the getRecordFromFormWithKey function. If PARENT_SCRIPT isn’t set, some of the child scripts run self tests on import.

The Kalva function is there to create the calf, the first set of lines set up the various field ID’s we’re going to need later and then grabs the Avelstjur value for the current record. If that isn’t set we have an error message and return. It then looks for a match of that ID in the Avelstjurar form and if it doesn’t find it, also returns out. I did fix the type to match as number with the same formatting everywhere because I think initially it didn’t match properly due to a type mismatch (Javascript is a little more picky than TF).

Once we’ve got the bull, we’ve started already with the mother, so we create a new calf from the mother (record.addNewRecordToField). This will automatically set the link from form field to this record. I also set Födelse to the current date, you could also default this in your form settings but this is an example of how you can set some fields in that child record. There is a document.saveAllChanges() because when you create new records, sometimes the scripting system can get out of sync so I find it easier to force a save here.

The last step is to add the calf to the bull and then save that change.

That should do the trick for what you’re after.

Did you use the tff file or the tfarc file? what is the difference?

TFF is a form template, it just has the definition of the form and linked forms. TFARC is an archive that can also include records. I did the TFARC first then noticed I was missing a form and used the TFF.

• This reply was modified 3 years, 11 months ago by Sam Moffatt. Reason: fixing blockquote tags

[Sam Moffatt]

The document.saveAllChanges() is after the return statement which means it never gets executed as the return statement returns control to the caller. It also looks like you’re missing a brace from the example code so I’m guessing that should have been before the function call. You can wrap the code in a back tick at the start and end to put it into code mode (or use the HTML tag):

function Focus_Script() {

    /* variables for Practice Plan */

    var practice_date = record.getFieldValue('fld-id'); //practice date in parent form
    var OT = document.getFormNamed('Offensive Transition'); // child form;
    var offensive_transition = record.getFieldValue('fld-id'); // watches checkbox in parent form;
    var date_text = JSON.stringify(practice_date);
    let foci = [];

    /* variables from Offensive Transition */

    var OT_date_id = 'fld-id'; // date field from child record;

    if (offensive_transition == true) {

        /* Search Offensive Transition for existing date and add record if not there */

        var records = OT.getRecordsForSearchTerm(date_text);

        if (records.length !== 0) {
            console.log("DATE EXISTS ALREADY"); // true condition;

        } else {

            foci.push("Offensive Transition"); // false condition;

            var new_OT_record = record.addNewRecordToField('fld-id');
            new_OT_record.setFieldValue(OT_date_id, practice_date);
            console.log("DATE DOES NOT EXIST");
            Utils.alertWithMessage('Done.', practice_date + " added."); // false condition;

        }
    } else {

        /* Delete existing record for a given date in Offensive Transition */

        var OT_records = OT.getRecords();
        var field_id = 'fld-id';
        /date field converted to text;
        for (var index = 0, count = OT_records.length; index < count; index++) {
            var aRecord = OT_records[index];
            var field_value = aRecord.getFieldValue(field_id);
            if (field_value == date_text) {
                OT.deleteRecord(aRecord);
            }
        }
    } // false condition;

    document.saveAllChanges()

    console.log("EOJ");
    return foci;
}

Focus_Script();

[Mark Kearney]

Hello. Basic programmer here (literally, my understanding comes from my days with BASIC).

I am trying to create a basketball season practice planner where clicking a checkbox for a practice topic automatically adds a record to a “Link To” field (joined by date field). The details for the parent topic can be stored in the child record. The practice topic from the parent form is then stored in array (foci) along with the other topics for that practice. If the checkbox is unchecked, the records in the child form are deleted for a given date and that topic is removed from the array.

This portion of the script (located in a field in the parent form) appears to work but the new child record does not appear in the linked field until records are refreshed. NB: There is a script in the child form that converts the date to text (as with var date_text below) for the search piece.

I would like to get the child record to show in the “Link to” field without needing to refresh. Any help would be greatly appreciated.

function Focus_Script() {

/* variables for Practice Plan */

var practice_date = record.getFieldValue('fld-id'); //practice date in parent form
var OT = document.getFormNamed('Offensive Transition'); // child form;
var offensive_transition = record.getFieldValue('fld-id'); // watches checkbox in parent form;
var date_text = JSON.stringify(practice_date);
let foci=[];

/* variables from Offensive Transition */

var OT_date_id = 'fld-id'; // date field from child record;

if (offensive_transition == true) {

/* Search Offensive Transition for existing date and add record if not there */

var records = OT.getRecordsForSearchTerm(date_text);

if (records.length !== 0) {
console.log("DATE EXISTS ALREADY"); // true condition;

} else {

foci.push("Offensive Transition"); // false condition;

var new_OT_record = record.addNewRecordToField('fld-id');
new_OT_record.setFieldValue(OT_date_id,practice_date);
console.log("DATE DOES NOT EXIST");
Utils.alertWithMessage('Done.', practice_date + " added."); // false condition;

}
} else {

/* Delete existing record for a given date in Offensive Transition */

var OT_records = OT.getRecords();
var field_id = 'fld-id'; /date field converted to text;
for (var index = 0, count = OT_records.length; index < count; index++){
var aRecord = OT_records[index];
var field_value = aRecord.getFieldValue(field_id);
if (field_value == date_text) {
OT.deleteRecord(aRecord);
}
}
} // false condition;

console.log("EOJ");
return foci;
document.saveAllChanges()

Focus_Script();

[Rob Naaijkens]

Hi Sam,

First, thank you very much for all this effort, appreciate that!

I had a link between the second and the first. But not between the first and second.

I used your script but got some errors. Now I still got an error on the script for the second form. Is there a possibility to copy my database and send it to you by e-mail (WeTransfer) so you can take a look?

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

At the moment it doesn’t look like you’ve got any links between the two?

The function doesn’t work because first nothing calls it (you need a getTotalofLinkedFieldForField(); at the end of the script) and second linkedRecord is not defined.

Since there isn’t a link between the two forms, we’ll automate populating the other form. We’re going to use form.getRecords to get a copy of all of the records in the current form (there are a bunch of other examples on the forum as well), get the total and populate that into the other form. We’ll create this script inside the first form since it’s the source. You’ll need to swap in the field IDs from the place holders:

var firstForm_NaamFieldId = 'fld-naamfield';
var firstForm_tableFieldId = 'fld-4dde0c4712954541a69b18d602bfcb27';
var firstForm_tableField_subFieldId = 'fld-60216b72e69b4a9bab98a23c8a019ea9';

var secondForm = document.getFormNamed("Nieuw formulier");
var secondForm_NaamFieldId = 'fld-namefield';
var secondForm_NummerFieldId = 'fld-nummerfield';

function Create_Summaries() {
	// get all of the records from the current form (should be first form)
	for (let currentRecord of form.getRecords()) {
		// create a new record for it in the second form
		let linkedRecord = secondForm.addNewRecord();

		// copy the name across
		linkedRecord.setFieldValue(secondForm_NaamFieldId, currentRecord.getFieldValue(firstForm_NaamFieldId));

		// create the total value field
		linkedRecord.setFieldValue(secondForm_NummerFieldId, currentRecord.getTotalofLinkedFieldForField(firstForm_tableFieldId, firstForm_tableField_subFieldId));
	}

	// save all changes
	document.saveAllChanges();
}

Create_Summaries();

Though ideally you’d set up a link to form field to keep them together. If you create a link to form 1:M from your first form to your second form and then tick “show inverse relationship”, you can create a script field in your second form that looks like this:

var firstForm_tableFieldId = 'fld-4dde0c4712954541a69b18d602bfcb27';
var firstForm_tableField_subFieldId = 'fld-60216b72e69b4a9bab98a23c8a019ea9';
var secondForm_linkFromFormFieldId = 'fld-linkfromformfieldid';

function TotalValue() {
	let linkedRecord = record.getFieldValue(secondForm_linkFromFormFieldId);
	return linkedRecord.getTotalofLinkedFieldForField(firstForm_tableFieldId, firstForm_tableField_subFieldId));
}

TotalValue();

Though for that to work you will need that link to form field setup. Give that a spin and see how it goes.