Or you could use a script that copies the value from the second form only when the target field is empty. This way, initially it is set by the script but later, you can overwrite the value if needed.
Cheers, Daniel
---
See https://lab.danielleu.com/tapformspro/ for scripts and tips&tricks
Thanks for your response. What you’re saying for new records I think would work fine. I can manually alter the table.
The tricky bit left is I don’t know how to populate the historical data from old <!– voldemort –> database into a table. I still need to use a calculation or script to get the data out of the table for sorting since I can’t sort by the table.
I’m going see what an export of records from a Form with usual fields and a Table from Tap Forms looks like. Perhaps I can manipulate the export from the old database into something I can then import.
hmm. nope. The table doesn’t export from Tap Forms. So I’m doubting I could import it.
Hey Brendan,
Thank you :-)
Saved seearches are very powerful but it’s not what i expected.
I wonder if we can do like a GTD app, if the task is checked, there is event after (color change, archive, move,…)
Maybe it’s possible with a script!?
The Calculation Field and Calculation Type settings on a Form just tell Tap Forms what the default calculation value to display is on the Single Column List View. Although it does display that result if there’s no Field level summary calculations set.
There’s a popup button that will display the aggregate calculations for all of the individual Field level Summary Calculations. By selecting a different one to display, you’re changing the Form’s Calculation Field. It has no bearing on the Multi-Column List View because that view will always display all of the individual Field level calculations.
There’s no Field ID available for the Calculation Field setting explicitly because that’s just a reference to the actual field in the form, which you can get the ID for either on the Script Editor as Daniel mentioned, or at the bottom of the Fields list on the Fields tab of the Form inspector panel.
There’s no ability to watch the aggregate calculation. But the Scripting engine does have functions for getting that value if you need to.
For example, to get the total of a field in a form, use:
form.getTotalOfField(fieldID);
You can of course get the form using either form if it’s the currently selected form or let other_form = document.getFormNamed('Other Form');
The Table field is a good use for being able to select a record from another form, and to be able to override a value (price) coming from the other form. That’s because with a Table field, the values are copied from the selected record rather than having a reference to the original record like in a Link to Form field.
Scripts execute automatically whenever a value in a field they reference are updated.
It’s triggered by the record.getFieldValue('fld-.....'); call. When you change a value in a field, Tap Forms checks to see if that field is referenced by any scripts or calculations. If so, it runs the scripts and evaluates the calculation formulas in those other fields.
Just thought I’d provide some feedback on my finding this useful to sort records by month. A saved search shows me everything in a year. Being able to use the pick list I use to enter the month on the record is nice, and I’m sure I’ll use this other places too.
I modified the script from the Script Manager a little bit. I wrap the offset with Number() to convert it to a number. It’s text apparently. (I’ll learn this javascript yet.)
Thanks for the useful resource Sam.
(I’m not sure how to preserve the tabs in the code. I thought the code tag would do that.)
// NAME: PicklistID
// VERSION: 1.0
/**
* PicklistID returns the offset index of a value from a pick list.
*
* This is useful for creating a surrogate field for sorting a field
* that uses a single value pick list by the ordering of the pick list.
*
* Reference:
* https://www.tapforms.com/forums/topic/pick-list-sort-order-2nd/#post-46660
*
* @param {string} keyfield_id - The internals Tap Forms field ID for the source field.
* @param {string} picklist_name - The name of the pick list to use for ordering.
*
* @return {(number|null)} The offset matching the value from the field or null if not matched.
*/
function PicklistID(keyfield_id, picklist_name) {
let key = record.getFieldValue(keyfield_id);
let picklist = document.getPickListNamed(picklist_name).values;
for(let offset in picklist) {
if (picklist[offset].value == key) {
// just because when I see it I want to see 1 for january
return Number(offset) + 1;
}
}
return null;
}
var month_id = 'fld-370f51968e3146dca058b82ef34b1592';
var list_from_picklists = 'Months';
PicklistID(month_id,list_from_picklists);
Just an update. I just realized I won’t actually be able to use the calculation at all for the price discussion earlier in this thread where I thought I had a work around. I need to be able to override the price. I’m back to needing to have it auto fill an Ad Price field based on a change from the Ad Size field. And then still be able to override the Ad Price field manually.
So I guess my question is, with javascript and the API, can I do that? Or will it run and write over when I choose recalculate formulas? I don’t understand what triggers a Script field type to run. So much to yet to learn about Tap Forms!
The field ID will not change. You get the code in the script editor: click on the name of the field and then on ID. This will insert the code in the editor section.
Cheers, Daniel
---
See https://lab.danielleu.com/tapformspro/ for scripts and tips&tricks
I know that in multi-column view, I can view the summation (and other stats) for any column using the sigma “row” at the bottom. I can also set the “calculation” for a form to a field and type.
I’d like to use the total of a column as a script trigger. It looks like to do that, I’d need the field ID for that value. Is that field ID per form immutable, and is there a way to get at that?
–Richard
And there may be no joy in Mudville. My hope was that this parent form would have summations for multiple envelopes. Naively, I expected that the “Link to Form” field was a per record field, since it’s, well, a field. But thus far, it appears that changing that field for one record in the parent form changes it for all records…so it’s effectively a form property, and not a field value. I can see why allowing multiple joins would be problematic, but it’s not as clear that having a per record one-to-many would bring down the house.
Is there a workable supported route for what I am trying to do? It’s starting to look like the parent may have to have a per-envelope aggregation script. Javascript is not my language of choice, but it looks like there’s enough functionality to do this without having form-to-form relational doings.
–Richard
You can use a Script field to concatenate values from your Link to Form field together. Build an array of values, then use array.join(", ") to join them into a single comma separated string. Or however you want to join them.
Thanks for that. That looks like it should accommodate when I need text and not a number. And I fully get having non programmer functions be the face of access. And also, thank you for participating in a forum such as this with people learning the ways of Tap Forms. It’s very helpful.
Looking up data from a field in another form is something I will do a lot. In the long term, I believe I will likely write some javascript that gives me what amounts to a field type I might call “Lookup”. For this one project, I’ll be satisfied however I can make it work.
In looking at the Formula for a Calculation field type, I had expected when I move a field to the formula area I would be offered text related functions in a function popup similar to when it’s a number I move. All that is available is COUNT for text. So it looks like when from another Form, the field that is moved over is an array of values. Because the Form I’m calling from is a field with a Link to Form type using Join, it should always be an array with a count of 1, since Ad Sizes will only ever have unique values in each record. So even if I could have a CONCAT of array values, that alone would be very helpful. Or a way to access individual elements of that array. Or even just the first element.
Anyway, that’s just food for thought for something I would find very valuable. Thank you again.
cheers,
Lane
So Tap Forms was designed to take SQL out of the picture for customers. So things like searching are done by creating Saved Searches using the UI.
You could build a Saved Search on your AdRates form that filters on size equals 'full page'.
Then in your Script, ask the Form for the Search with whatever search name you assigned:
var adsSearch = form.searchNamed('Full Page Ads');
Then you could simply grab the first record from the array of records returned from the search.
var firstRecord = adsSearch.getRecords()[0];
Now you can get the value for the field from the firstRecord:
var price = firstRecord.getFieldValue(price_id);
Something like that at least.
Thanks!
Brendan
So in the ad-rates form, you have one record that defines all the different prices?
I would change this to one record per size. Then you can use a joined link-to-form field to link the two records based on the size. Next, use a calculation field to fetch the pricing from the linked form. No javascript needed.
Cheers, Daniel
---
See https://lab.danielleu.com/tapformspro/ for scripts and tips&tricks
I tend to think in terms of sql when it comes to databases. How would I translate an sql query sort of like this into a script where the script is in one Form and adrates is separate Form.
SELECT price FROM adrates WHERE size = ‘full page’ limit 1;
