Given I have a form with a list ad sizes and ad prices. Something like:
form:
ad rates
fields:
ad size ad price
full page 1000
half page 500
quarter page 250
new form:
I know how to choose from the list I made to populate from the ad rates form filling with ad sizes as a popup. How can I have the price autofill from the ‘ad price’ field that corresponds to the ad size chosen from the popup?
What I’m used to is a lookup option that watches for changes in my field where I choose the ad size and updates the price to the corresponding entry from the ad rates form. Can someone offer guidance here?
The Table field is a good use for this sort of thing.
Take a look at the Invoices sample template:
https://www.dropbox.com/s/6rkk6413cbv3aj1/Invoices.tapforms.zip?dl=1
In there you’ll see an Orders form that has an Order Items Table field in it. The Order Items field is connected to the Products form. When you click the checkmark button to choose a record from the Products form, Tap Forms will copy multiple fields from the Products form into the Order Items Table field, including the product name and price.
Another way to do it is to use a Script field that fetches the value you want from another form. But that’s a bit more complicated to achieve.
OK. So a multi field fill instead of a lookup. A table is basically like a form, but a sub form of its parent form. It’s always going to be a table with a single row in this case. And I guess I can’t sort a layout by the ad size field in that table, which I need.
So I’m trying to pull data up to the main form using a calculation. I can see how I can do a total of the ad price from the table onto the main form. But I can’t seem to get the ad size out. I’ve tried using a concat. All it seems to allow is a count, which is auto inserted with the field when I move the field into the Formula space. Trying to change this to a concat isn’t understood.
It’s not ideal that I would use a calculation for this anyway, as I may need to override or otherwise enter a custom value. I can live with it if it has to be a calculation. Would so much prefer an autofill I can change.
Would a script allow me to manually overwrite a value it returns? I wouldn’t think so. Perhaps I could add an ‘override price’ field the script would read from and return. Awkward looking, but would do the job.
OK. I’m committing to using javascript for this. Just when I think I’m out they pull me back in!
When using a script, do I do it something like this?
select a record from my Ad Rates form where Ad Size = the value of Ad Size on my Orders form? And then get the value of the Ad Rates field on the selected record?
reading the API right now.
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;
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.
It’s many records in the Ad Rates form. A field for Ad Size, and a field for Ad Price. Basically. Apologies if I made that sound muddled. It’s my first language.
I’ll try what you’re suggesting and shall report back.
ah. ok. I had started down this path before. I hadn’t made the leap that I could pluck data out of a linked form. And I was also thrown by the data showing in the linked form not changing when I would select a different ad size. But that’s fine as long as it gets the correct Ad Price, which it is. I’m doing a ‘TOTAL’ in the formula for the ad price, as that’s as good as any of the other options presented I suppose. This seems to work as I need it to.
Thank you muchly!
Great that this works for you!
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
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
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.
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 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.
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.
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.
I think that’s the way to go. I may code some kind of option to override or alert. A feller could select the wrong one by mistake and need to fix it.
Table fields can be exported as a CSV file when you enable the Export Linked Records option on the Export Records screen. Tap Forms will create a separate file for each Table field. But you can’t import them into a Table field since there’s no form associated with them like with a Link to Form field.
.
