Search Results for 'script'

[Brendan]

You could do something like that using a Script field instead of a Calculation field. You’d just have to fetch the values from the field from another form using the value from a field in your current form. I’m sure there’s more nuances to the VLOOKUP function. I’ve just never used it before.

[Sam Moffatt]

A few thoughts, not sure if these will help solve your problems. I’d start normalising your data a little because that will help you aggregate on those axis (e.g. https://en.wikipedia.org/wiki/Database_normalization).

The first step I’d take is to create a form with hosting provider in it. Each hosting provider is a record in this new form. Then you can use a Link To Form field as either a 1:M or JOIN type to go from the hosting provider to the customer (1:M assuming each website/customer is only hosted at one place, you could do M:M if that isn’t true; if you have a “key” column for the hosting provider in place you can use this for the “JOIN” field, a text field with a controlled value is enough).

Doing this will force you to create a record per hosting provider however the Link To Form inside of it will give you quick aggregations on the numeric fields. See a screenshot of roughly what I mean. This might not be everything you need but you can use scripting to create a text entry with the data you want for that hosting provider. At the very least this makes filtering by hosting provider relatively trivial and building JavaScript aggregations a little easier. It is a little more work to get set up but you should be able to experiment on a backup to see what I mean.

If you go for the JOIN option, you should be able to do this by creating the hosting entries you need. You could automate that but that might be more hassle than it’s worth unless you have a lot of hosting providers. If you go for the 1:M or M:M options, then you need to link all of the records together by hand or again write a script to do it for you. Either of those choices will make sense depending on the volume of data you have and your own inclinations. As always before doing any of these tasks: make a backup first, restore it to a new document and disable any sync you have enabled to prevent changes from propagating. That way if you make a mistake, you can be confident you didn’t toast your only copy.

Attachments:

You must be logged in to view attached files.

[Anonymous]

Hi Sam, thanks for the info i’ll try it soon.

I’ve got a Numbers sheet with customer data comprised of names, mail, website, where it hosted, prices etc.

Got most customer fields already in 1 form and prices etc are calculated correctly through scripts. Now I want to make an overview form which show me total prices, average monthly income / expenses, how many websites are hosted with a specific holster etc.

As an example I asked the question because I want to have an overview how many sites are hosted with hosting provider x, y and z. Can’t do that with an calculation (Numbers got an COUNTIFS, tapforms not) and calculations are not updating in my case so I’m using the script function (also gives me more advanced features and also easier for me to understand as a coder)

[Sam Moffatt]

You need to do a loop for that:

var customers = record.getFieldValue('fld-a538b0e58afa4d5db40dd06a19af2436');
for (var index = 0, count = customers.length; index < count; index++)
{
  var name = customers[index].getFieldValue('fld-c4e6710f99544905b9cbbf1fcbd8f613');
name;
}

That will overwrite if you have more than one field linked.

The Script Editor will only display the fields from linked forms however you can copy and paste the field ID’s and keep track of them manually. Linking makes it a little easier but the ID’s in the script itself is unrelated to any links. You can use documents.getFormNamed('Customers').getRecords() to get all of the records in that form instead of using record.getFieldValue.

Do you mind sharing a little about your use case and the problem you’re trying to solve?

[Sam Moffatt]

I use a script in one form to create a new record in another form. On Mac if you create a form script, you can create a new record based on the currently selected record. This creates a copy of the data, not a reference, so if it changes in one place it doesn’t change the other which may or may not be problematic for you.

You can do it a couple of ways, you can get the form from the document and create a new record (var newRecord = document.getFormNamed("Bookings").addNewRecord()) or if you create a Link To Form from your Leads form to your Bookings for you can add a new record via (var newRecord = record.addNewRecordToField(link_field_id)).

Then with that newRecord you basically map across the fields from the two forms to update them.

Here’s an example using the addNewRecordToField approach that I use. It’s slightly simplified example but basically it gets all of the values from the record, adds them to a dictionary and then uses that to save the data to the new record. For what you’re doing this might be useful because you can see which leads lead to the booking.

// Pull some details from this record
var title = record.getFieldValue('fld-39ca9564ef2347ac93f933bc9a2316ac');
var price = record.getFieldValue('fld-a2973999a60d4319baf0b4480d6f57a0');
var note = record.getFieldValue('fld-d0cfab9ec09d497294fbd8b5b52caf16');
var line_number = record.getFieldValue('fld-f95b68d488cb4b058bbf3de84e1a7c3b');
var purchase_date = record.getFieldValue('fld-bc2e4b152dee42ac9361539a6e37cb5d');
var marketplace = record.getFieldValue('fld-fa37906add2942c88bce3b500561c42d');
var order_id = record.getFieldValue('fld-8228040b4641449b96aabfaea15f1ac5');
var store_name = record.getFieldValue('fld-c153da2f9a504be4b6fee4b5b62a1c11');
var ship_date = record.getFieldValue('fld-6ab700ccc11d418fbd27d8899d00c7a9');
var delivery_date = record.getFieldValue('fld-4b1c4180dc1b4bb08b32d16fa60cae66');
var purchase_key = record.getFieldValue('fld-3e49aaa5bc32429c8f0f0f234878356d');

// Details field names
var details_title_id = 'fld-0d0edd2552ea461e929f806a4e5552b5';
var details_price_id = 'fld-08129d71ab0f4fa4a2749456281fca07';
var details_notes_id = 'fld-bf19d52c18cb4f5198df191ef7902e1b';

var details_purchase_date_id = 'fld-ccbd9a8f51d34246bebfb31aa4e397dd';
var details_ship_date_id = 'fld-cb3a9886ac7f4ec487447801a3911a1a';
var details_received_date_id = 'fld-bb17d48e41c7423692ab586f6c884d05';

var details_order_id_id = 'fld-e3e66a0f2e5c4df7b9496f65355e0bcf';
var details_marketplace_id = 'fld-c163aba17ae64c4d93b5a53819a139dc';
var details_store_name_id = 'fld-3d98dc6cdcae4a88909c97c80dde7bfb';

var details_state_id = 'fld-9402f8c0d53c43b986fee4ebc3468929';

var details_shipping_tracking_number_id = 'fld-6ea45a9c141343628940bfbcfa38ee90';
var details_shipping_carrier_id = 'fld-12644a7a4ae24ed8a7926123832d3557';

var details_purchase_key_id = 'fld-8be9b2c2603f458f8349082237c41964';
var details_order_line_number_id = 'fld-da763fa0946d4be79039b4e828cf85f4';

var data = {
	// Order Item Details
	[details_title_id]: title,
	[details_price_id]: price,
	[details_notes_id]: note,
	[details_order_line_number_id]: line_number,
	
	// Order Details
	[details_purchase_date_id]: purchase_date,
	[details_ship_date_id]: ship_date,
	[details_received_date_id]: delivery_date,
	[details_order_id_id]: order_id,
	[details_marketplace_id]: marketplace,
	[details_store_name_id]: store_name,
	[details_purchase_key_id]: purchase_key,
};

// Last but not least push the new record.
var details_id = 'fld-ac04de32d98242b88333977c89526fc1';
var detailsRecord = record.addNewRecordToField(details_id);
detailsRecord.setFieldValues(data);
document.saveAllChanges();

One of the advantages of the script approach for me is that it let’s me splice in extra data or set new values at creation time as well. Another trick I do is use a form script to use a prompter to ask for extra data and integrate that into my new record.

Scripting manual section: https://www.tapforms.com/help-mac/5.3/en/topic/scripts

[Kana Ne]

Is it possible to get the value from a field on an other form in a script?
I can’t see the fields from another form in the script window of this form.

Thanks,
Kana

[Brendan]

You can create a Saved Search to filter your records, and then have a Script run against just the found set. But not on arbitrarily selected records.

[Bert Rietveld]

Is it possible to have a script acting on (a few) selected records rather than all or one?
I’d like to use that for a ‘merge records’ script. The map view is great for seeing that a couple of records are for the same (or close) location even when they have (slightly) different names. I could then select them and merge those records.

[Stephen Martin]

Thanks, Brendan.

P1) I have to meet a form design, so the list mode (Table of Records) won’t work for me.

P2) I had not figured out what the parent/child relationship and capabilities were, so you’re description answered my question. Thanks.

The only way I can get to my need then, is to use pre-printed forms with the headers; then print the labels layout or vice-versa. Thx.

[Gilles Cruypenynck]

Hi,

Would Javascript (used within Tapforms) allow me to create folders on disk named after some data from Tapforms?
Think, for example, of a students database and creating folders named based on their family name field.

I don’t know JS and thus whether I should spend/waste time trying to write such a script.

Any help or advice will be much welcome.

Regards

Gilles

[Kimberley Hoffman]

I created this so I can search my bullet journal for important entries. For some reason the background (the dotted ‘paper’) didn’t export correctly with the file, so I’ve added it to my zip-file.

It contains entries for your bujo number, for when you started and finished the bujo, a photo drop for your index pages and highlights as well as file drops for your calendar and future logs, which you can change to a photo drop before beginning your entries.

For the headlines I’ve used a free font Blenda script and a pay font – Blend script – for the date entries. But you can use any font on your system.

I hope you enjoy it!

Regards,
Kimberley

Attachments:

You must be logged in to view attached files.

[Brendan]

You can already reference parent fields in a Calculation field or Script field if you’re using a Link to Form field.

You need to enable the Show Inverse Relationship option on a Link to Form field to get that though.

But you’re talking about a Table field. I haven’t enabled access to parent fields in a Table field. I think you’re the first one to ask for it. I’ll think about it for a future update to see if it’s possible given my current architecture.

But to me it sounds like you want to go the other way. From parent to child. So if you change the number of servings in the recipe, you want the quantities of the records in the Table field to update accordingly. You can already do that with a Script field on the parent form. The Script field could be triggered to run whenever the value in the Servings field changes. It would loop through all the child records in the Table field and update the quantities. I’m assuming it would just multiply the new serving size by the quantity in the ingredient.

There’s a Snippet on the Script Editor that’ll loop through all the child records.

[Barry Shevlin]

Thank you, Brendan. The downside to that, of course, is that with the show edit/save button off, all empty fields are displayed.
The text alignment issue seems spurious: only some of the text fields, on some of the records, some of the time. And once re-aligned (left), they don’t necessarily stay realigned!
I’m more than happy to not have the edit button but not so happy to have empty fields displayed.Is there a way of having my cake and eating it? A script perhaps that hides empty fields?

[Brendan]

Modifying a child record should trigger a parent record’s Script field to run. But ya, it seems that just adding a record does not seem to trigger the parent record’s scripts to run. Deleting a child record and modifying a child record does though. I’ve just worked on a fix for that.

[Dave Emme]

OK, great! That helped a lot; thanks.

Now I have one more question (or feature request?):

I have a database of phone calls, with a parent form “Callers” and a child form “Calls”, linked via the phone number. Callers has a join to Calls, to display a nice table of related calls in the Callers record.

What I’m trying to do is extract the number of call records and the date of the last call from the related Calls form records and place those values in fields in the related Callers form record.

I have a script field in Callers with a script which extracts the wanted values and inserts them in the Caller record. The script works fine. Per your info above, I can automatically trigger the script by referring it to a changed field in the Callers record, but apparently only a “real” data field in Callers. What I would like is to have the script run automatically when I add a Call record, by clicking on the “+” in the Calls table joined to the Callers record. But if I try to make my script trigger on changes to that join field, nothing happens, even though it has effectively changed. So I have to manually trigger this script by making a change to a “real” field in the Callers record after adding the new Calls record.

Is there something equivalent to an “event” which will run my script when I add a record to the related Calls form/table? (Maybe this is something I should know, but I’m not (yet) a JavaScript programmer)

Thanks for your help!