Link to multiple records in another form – and search

[George Cx]

Apologies – the only reference I could find to this was from years ago – so I think I might be missing something.

Here’s the situation.
Simple example:
Let’s say I have a form – say wines.
And I want a record of which store I bought it from. Let’s say I want to have records of those stores in a separate form, not just a multi-check box.

So I can set up a record in Wine and link it to the Store form. And I can make it multi – so I can link to a bunch of stores.

But because it’s a multi link it just shows that field as blank in the overview – if you click into the record you can see there are two stores attached in the source form. But not from the multi/column overview.

Which seems to mean it’s impossible to search eg I can’t go to the wine form – search on “store a” and see which wines have “store a” in their source options.

I feel I must be missing a trick – this seems to be handled relatively elegantly with AirTable – but all my data is in TF and AT is too much for a number of reasons – so I’m sure I’m missing some smart search function or clever way to attach (I could create five source fields and just use a single link – but that seems a bit clumsy).

Anyway would love to figure this out – I’m doing this all on iOS and don’t have Mac – which might also be an issue I realize.

Thanks again.

[Sam Moffatt]

I’m a little confused because if you have two forms, one for wines and one for stores, which have a many to many link between the two and you’ve ticked the “show inverse relationship” on both sides (make sure to toggle it if you changed the type to delete and recreate the field), then on both sides you should have a multicolumn list view embedded in your record that gives you what I suspect you’re after (e.g. when looking at a wine, you can see which stores you linked). Try setting Many to Many and then toggling the “show inverse relationship” option on the “Link to Form” side of the relationship. This should be ok on either a Mac or iOS though I always go and double check it removed the field after toggling it off and then going all the way back to toggle it back on (that’s much easier on the Mac).

On another tangent, I think this might be similar to an earlier thread on how to filter using fields in two linked forms. The suggestion I made there is that it might help to have a third form, in this case I’d probably call it “purchase”. The “purchase” form has a 1:M link from wine to purchase and 1:M link from store to purchase, maybe put a date in as well. Then you can use that inner form to track which store your wines came from and when.

It could also be that the indexing in general is problematic and you need to copy the values from the linked forms into the parent. I wrote an example of how you can use a script field to copy values from linked records to help them show up in indexing.

I think maybe the Link from Form field might have gotten fried so give that a spin first because this should be working. Otherwise the other ideas might help you out. I’d suggest the middle one is useful to do because it let’s you track individual purchases but maybe you’re less OCD than me :D Good luck!

[George Cx]

Hey Sam – thanks for such a prompt thoughtful kind and full response. Awesome.

I don’t think I explained this well.

Here’s a quick screen cast of the situation with simplified data.
https://photos.app.goo.gl/hUQknV1RSUkJFcuU6

So you see I have three wines and two stores.
Within the record of the wine I can absolutely see the stores when I click on that field.
And within the store record I can see the link back to the wines. So far so good.

What I’m trying to do is at the Form level run a search so I could see – oh I want to get Wine A what stores is it at. Or I’m at store A which wines do they have.

The problem is the multi-select doesn’t port over that field text to the high level form – I can’t search. I have to go through each record in turn.

And one wine can be at multiple stores. Each store can have multiple wines.

I’m intrigued by the idea of creating a “join” form – but I think it’s going to have the same problem, I wont be able to easily search.

This is what AirTable looks like. The multi link shows up in the record – so I can search on it easily. I feel like I’m overlooking something – so appreciate your patience and generosity here. I’m on iOS so the possibility of writing some fancy Java to run a search is limited (also by my lack of coding chops) but I’d there is some way to run code to do this I’m all ears.

https://photos.app.goo.gl/4RW88K7fybqgce9v8

Thank you again

[Brendan]

Hi George,

You’re right. Tap Forms does not search down multiple levels of relationships.

But you can work around that by writing a Field Script which loops through the child records and builds a string that can be displayed and searched on the parent form.

Use the Child Records Loop snippet to loop through your child records. Then pick out a value from the record to concatenate to a string. Return that value and then you will be able to search that string.

function recordsLoop() {

	var actors_id = 'fld-984cf79ccafb45a381b0f95b0aa28e78';
	var last_name_id = 'fld-a907066570844290a27940d34de98b4f';
	var actors = record.getFieldValue(actors_id);

	var last_names = [];

	for (var index = 0, count = actors.length; index < count; index++){
     	var last_name = actors[index].getFieldValue(last_name_id);
		if (last_name) {
			last_names.push(last_name);
		}
	}
	return last_names.join();
}

recordsLoop();

Probably not as convenient as what Air Table has. I just read an article in the BBC News that they’re worth a billion dollars now. So I suspect they have lots of engineers working on the app and can do some things that Tap Forms can’t do yet. I’m just 1 guy :)

Thanks,

Brendan

[Daniel Leu]

I had a similar problem the other day and just wrote a small script that concatenates the content of a child field. The result is shown in the script field. Since this is only a helper field and I don’t want to clutter my form, I set the hidden flag on the script field.

const wineryNameId = 'fld-3e98efea365847f3a2569700d679b4ed';
var winesLinkId = 'fld-30dd9906b79649b6b54c01603928901d';

function joinChildField(childLinkId, fieldId){
	let entries = record.getFieldValue(childLinkId);
	var list = Array();
	for (entry of entries){
		list.push(entry.getFieldValue(fieldId));
	}
	return list.join(', ');
}

joinChildField(winesLinkId, wineryNameId);

In order for you to use this field script, you have to set the different field ids. wineryNameId is the field in the child form. winesLinkId is the linked field pointing to the child form.

The operation of this script is very similar to what Sam posted. It is simpler as it doesn’t contain any logging features. The only thing you have to do is updating the two field ids.

Obviously, it would be nice if search would go through child records, if requested. And then only show child entries that match.

[George Cx]

Hey everyone, thanks for the awesome responses.

First up, apologies to Brendan. I wasn’t trying to be unkind – TapForms is awesome – and is in pretty much every way far more flexible and cool than AT. Its amazing what you’ve created – and I only wanted to give an example to show what I was seeing, not to criticize – thank you for taking it in that spirit (and I’m rooting for your first billion! Lets have this conversation on your yacht!)

Second up – I’m sorry to be such a dummy, but I’m not quite sure how to make these scripts work their magic on iOS.

Firstly I went to one of the records, selected “run a script” from the top right menu, then pasted in Brendans code, saved it, then backed out, then went back in and ran the script. Much as monkeys throwing screwdrivers at a typewriter to write Shakespeare, it created the exact response I’d feared ie nothing, because I guess I’m not sure where the results are put, not sure how to figure out the specific variable names of my field ie var actors_id = ‘fld-984cf79ccafb45a381b0f95b0aa28e78’; var last_name_id = ‘fld-a907066570844290a27940d34de98b4f’; ie I’m assuming that long key is particular to the field of the record. (I note that Brendan and Daniel have different field references but I’m assuming thats because they ran this on their unique instances of their forms.)

Then I backed out and edited the form itself, added a “script field” pasted in the text. Then pulled up the record and there was the script field. But this time even the typewriter was undented – the field sure said script, but clicking on it didn’t seem to invoke anything.

If I could lean on everyone’s kindness and expertise one more time and assume you are teaching this code to a fifth grader (and not one of those really bright fifth graders) – is there a screencast or stepwise instructions that goes step by step through
a) how to set up the code so those long keys actually related to fields my system will recognize
b) where to paste said code
c) how to invoke, and where to look for the results (and/or how to set up a special field to capture results*)

*What I’m sensing will/should happen is that the code goes into the “linked” field – scrapes the returned value (say store name) then pastes “store name” as text ideally in some dedicated field so it shows on the multi-column view – then you can search/filter on that text. Anytime you update that record, you run the script again to make sure the latest scrape is in that text field – am I close?

I could paste you a quick screen cast of my pitiable attempts, but just imagine a whole lot of nothing happening and you’ve got the gist.

Thanks again for the pointers on this, I’m hoping my cluelessness might help others on the forum.

Cheers y’all

[Daniel Leu]

I have to admit that I do all my development work on a desktop…. So I’m discovering the process along with you :)

Let’s assume you have two forms in your document, Store and Wines. When you see them, there is an ‘Edit’ button on the top right. Click on it. Now you can edit the forms. Select Wines.
I currently have three fields: ‘Wine Name’ (text), ‘Store’ (linked field to Store form, many-to-many type because I can buy the same wine at two stores and the stores usually have many wines, inverse relationship selected), and ‘Store TXT’ (field script). Just for completeness, in the ‘Store’ form, I have ‘Store Name’ (txt), ‘Wines’ (link), ‘Wines TXT’ (script).
Click on ‘Store TXT’ and then ‘Field Options’ then ‘Script’. This is where your field script resides.

You already noticed the Brenan’s and my field ids are different, so will be yours! To get the one you are looking for, touch in the text field somewhere to get the keyboard view. There is a small row with symbols. The one between fx and ABC is of interest. No idea how this symbol is called. Anyway, click on it. On the top, you can select between ‘getFieldValue()’ and ‘field_id’. The background behind these two options is ugly, looks like a bug where the background is not cleared. Here we need field_id. So select field_id.
Since we are in the Wines form, we need the id of the ‘Store’ field (link to stores) and the name of the store.
So click on ‘Store’. Now you are back in the editor with new text like
var store_id = 'fld-....';
Next we need the id of the store name. So again get the keyboard view and click on the icon between fx and ABC, click on ‘Store Name’ and you are back in the script editor with something like:
var store_name_id = 'fld-....';
These are the two items that are custom to your from and which are different to ours.

Now finish the code as follows:

function joinChildField(childLinkId, fieldId){
	let entries = record.getFieldValue(childLinkId);
	var list = Array();
	for (entry of entries){
		list.push(entry.getFieldValue(fieldId));
	}
	return list.join(', ');
}

joinChildField(store_id, store_name_id);

The result will appear in the ‘Stores TXT’ field. Now you got to do for the ‘Store’ form and then you can search.

Hope you were able to follow along….

Update: Added my TF document for your reference

Attachments:

You must be logged in to view attached files.

[George Cx]

Hey Daniel – I got an email alert from this thread, but now don’t see your message here. Your help is AWESOME – and I would never have figured this out.

I’m not sure quite what to do – I need to loop back with you on something – but I figure if I dive in here without the context of the email I got, then its going to be confusing for everyone else. I don’t want to post the email alert because I don’t want to post something you wrote without permission. And the reply email is just “support@tf” so I’m not sure its going to get to you.

1. I guess this is my request, can I post your email – so we all have context?

2. And my followup (which will only make sense to you) is exactly where am I putting these variables ie is childLinkID the same as var store_name_id, and field_id is the same as var store_id?
When you write “return list.join(&#039:,&H039;);” do you mean to replace the 039s with store_name and store_id? and if so which order to put them in.

SO CLOSE!
Thanks again.

[Daniel Leu]

No idea what happened to my comment…. you can repost it for context. No problem with me.

Good thing I still have my test document…. store_id and store_name_id are variables that need to be defined in your script. So this all belongs together as part of the script field options > script. This way the script know from where to fetch the necessary data.

var store_id = 'fld-xxxx';
var store_name_id = 'fld-xxxx';

function joinChildField(childLinkId, fieldId){
	let entries = record.getFieldValue(childLinkId);
	var list = Array();
	for (entry of entries){
		list.push(entry.getFieldValue(fieldId));
	}
	return list.join(', ');
}

joinChildField(store_id, store_name_id);

The function definition uses parameter names that make sense for different applications. So when you call this function with joinChildField(store_id, store_name_id);, store_id will become childLinkId and store_name_id becomes fieldId inside the function.

[Brendan]

@George, on the iOS version in the Script Editor, there’s a button on the toolbar just above the keyboard that kind of looks like a vertical set of boxes. Those are to represent fields. When you tap on that, Tap Forms will display the list of fields with a function selector for getting the field value or the field ID. When you tap on the field, Tap Forms will insert the variable into your source code, thus giving you the field ID or the value from the record and field that you’re looking for.

[Author]

Posts