Calculation Question

[Dave Yuhas]

I’m on a road trip in South America. I’m keeping track of mileage/country [mileage_out -mileage_in]. What I don’t know is how to keep track of multiple visits to the same country. I know I could keep one record/country and edit the record to update the mileage, but I want to create records for each visit. How can I get the total mileage driven in a country when there are multiple records for that country?

I hope that makes sense.

[Brendan]

Hi Dave,

You could create a parent/child relationship between two forms by adding a Link to Form field from the parent form to the child form. So perhaps you have a Country form and a Visit form. You would add a Link to Form field to your Country form that links to the Visit form. On the Visit form you would have your mileage fields and the calculation field. Then for each country you could see the list of visits with the total mileage for the lot for that country.

Hope that makes sense.

Thanks,

Brendan

[Mike Schwartz]

Brendan,

To keep my Tap Forms skills fresh, I implemented the two-form solution you suggested in my “sandbox” area. Some observations:

1. Works great just the way that you suggested.
2. After enabling “Show Calculations Row” in the Parent link-to-form field, I didn’t see the desired total for trip mileage. It wasn’t obvious to me that the way to get the totals to appear was to go back to the Child form and specify the Summary Calculation as “Total” for the calculated mileage field. It took a bit of fiddling to work that out. I hope that mentioning this helps things go more smoothly for Dave.
3. I created a custom layout for the Parent form. I noticed that the rows in the link-to-form field are a fixed height. If I make the text large for that field, it just gets chopped off. Shouldn’t the row height adjust itself to accommodate the font size? This might be related to another problem that you recently addressed.

Thanks,
Mike

[Brendan]

Hi Mike,

1. Awesome!
2. Ya, that could be better implemented. There should be a popup button just like on the multi-column list view for every field that can have a summary calculation.
3. Yes, it should scale. I’ll work on that.

Thanks,

Brendan

[Mike Schwartz]

Great!

One more thought: As Dave continues to add records to his Visit form, he would need to switch to his Country form each time, select the record associated with the country of his latest trip, and manually add the latest Visit record to the list of linked records.

As an alternative, could the process be automated through a special “look-up link-to-form” field? For example, the Country record with CountryName=Bolivia would automatically have its link-to-form field populated with all records from the Visit form where the Country field contains “Bolivia”. And the Country record for neighboring Brazil would automatically show all the trips to Brazil, etc.

I could envision this type of look-up field working with a variety of look-up criteria, such as text matches/contains, dates within a range, or numbers within a range, for starters. Ultimately, all the criteria you make available for general record filtering would have some utility as lookup criteria for the linked field.

Just a thought.

Thanks,
Mike

[Brendan]

Hi Mike,

I do plan on having an enhancement to the Link to Form field that asks you for a parent field and a child field and gives you a selection of operators to pick from, such as equals, less than, greater than, etc. to do some automatic matching. Then Tap Forms would build the linked records list for you based upon the value in a field on the parent record that matches the values in a field specified from the child form. It’s something I’ve been thinking of for a while. But I may need to wait until Couchbase Lite 2.0 is finished being developed because they are making some major changes to the way queries and the data is stored which would affect my plans for that.

Thanks,

Brendan

[Author]

Posts