Tap Forms for Magazines

[pierrot_rennes]

Hi,

Is there a form to list articles in magazines?
The idea is to create an index from keywords and then from one of the keywords, extract the corresponding articles from the list as a list on the screen.
I took the Book form which I modified and I created a table for each magazine.
In this table, I enter the different pages and articles with three fields (Page, theme, title)
But after, I don’t know (is it possible in Tap Forms) how to get the list of articles of all magazines from one of the keywords (Theme)
In the screenshot, I put the form created with a table.
But maybe this is not the right solution?
I’m listening for your ideas ;-))

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

If you only had one keyword, then I think a JOIN link to field type would work for you because you could have a single “keyword” record and then it’d automatically JOIN on other records. If you made “articles” it’s own form and used “Link to Form” fields to join it all together then that would work. You’ve gone with a table approach which can’t be used to handle the JOIN.

If you wanted to keep the table, what could work is to leverage a script to maintain the links. I did a really quick one here;

function Build_Keyword_Map() {
	// This is the other form that has our keywords in it.
	let keywordForm = document.getFormNamed('Keywords');
	
	// This is the field ID in the "keywords" form for the link to field to Magazines.
	let magazines_id = 'fld-b4724a28d4094b3faa9910f18374588f';

	// This is the field ID in the "keywords" form for the keyword field.
	let keywords_keyword_id = 'fld-ebaf7ffa3d484834bdcc34d3ffb9c5f2';
	
	// This is the field ID in the "magazine" form of the table.
	let articles_id = 'fld-dca513dfa3894e8aaa3792a9eb09b106';
	
	// This is the field ID in the "magazine" form for the keyword field in the articles table.
	let keyword_id = 'fld-055da0f29f764ea399e6f317427ff8ed';

	// This is to store a map of keyword to keyword record.
	let kwRecords = {};

	// Build the map of keyword to keyword record (cache if you will).
	for (let keywordRecord of keywordForm.getRecords())
	{
		kwRecords[keywordRecord.getFieldValue(keywords_keyword_id)] = keywordRecord;
	}

	// Iterate over every record in this form...
	for (let sourceRecord of form.getRecords())
	{
		// And iterate over each row in the articles table...
		for (let article of sourceRecord.getFieldValue(articles_id))
		{
			// Get the value of the keyword field...
			let keyword = article.getFieldValue(keyword_id);
			
			// Skip it if it is empty...
			if (!keyword)
			{
				continue;
			}

			// Check if a keyword record exists for it...
			if (!kwRecords[keyword])
			{
				// Create keyword record if it doesn't exist...
				console.log('Creating keyword record');
				kwRecords[keyword] = keywordForm.addNewRecord();
				kwRecords[keyword].setFieldValue(keywords_keyword_id, keyword);
			}
			
			// Link the keyword record to this record.
			kwRecords[keyword].addRecordToField(sourceRecord, magazines_id);
			
			// Save the changes to make sure the link persists properly...
			form.saveAllChanges();
			
			// Log the keyword we processed, we're done!
			console.log(keyword);
		}
	}
}

Build_Keyword_Map();

There are comments throughout which should explain what it does and I’ve attached a sample archive with the script in it inside the “Magazine” form. You can play with it to get it to add links. What it doesn’t do right now is handle if you delete or remove a keyword. You’d also have to do a split or similar to do a comma separated list of keywords as well but this is a quick example of how you can use scripting to build that link.

If you change from a table to a child form then you could link a single keyword easily. If you wanted to do multiple keywords (e.g. Big Sur, Photos) then you’d be back needing a script again.

Hopefully this helps :)

Attachments:

You must be logged in to view attached files.

[pierrot_rennes]

Hi Sam,

I put only one keyword per article to keep it simple
If I really need a second keyword for the same item, I create a second record
Apparently, your script works according to my need.
I will test by adding some articles.
If it’s ok, I will start from your model to modify your form and adapt it to my needs
A big thank you for your responsiveness, I will tell you when it’s good

[pierrot_rennes]

Hi Sam,

I just did some tests
Ideally, in the Keywords form, the page and the subject should be displayed in the table
I tried with the properties of the array but it is not possible
I’m putting you a screen copy
This is not urgent
thank you in advance

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

That’s a limitation of that particular model because you can only link to a form record not an entry inside a table field. It looks like what you’re after is more the article than the magazine so if we pivot a little to make the ‘Articles’ their own form then you can skip the script and just use a Link to Field with JOIN and I think it’ll give you the read out you’re looking for.

Here is a new TFArc with a new Articles form setup as a Link to Form JOIN to the Keywords form and also setup with a Link to Form 1:M from Magazine to map that back. Inside of Articles I added two calculation fields to map down fields from the Magazine form into the Articles one which can be seen in the list view in Keywords. The existing fields are still there but with some renames.

Attachments:

You must be logged in to view attached files.

[pierrot_rennes]

Hi Sam,

Sorry to ask you again
I made several tests with your form
But I think I didn’t get it all or something’s not working
We have three forms: Magazine, Keywords, Articles
The main form is Magazine so it is in this one that I enter all my articles with:
The title of the magazine
The number
The date of publication
Keywords
The pages
Subject title

It is in the Articles table (link) that I must enter, isn’t it?
What is the Article table for (Table0 in the form?

When I run the script, the new records are not added in the Keywords form (only in the Articles form)
Did I do something stupid or change something without realizing it?

Another question :
For the Magazine Month field (calculation type), I cannot display it in a date format type MMMM YYYY

To sum up, the Keywords form is the most useful to me.
It allows me to consult the table which lists all the magazines for a specific keyword.

I have attached the 3 forms that you had made at the beginning.

Thanking you for the time and the help you give me

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

It is in the Articles table (link) that I must enter, isn’t it?
What is the Article table for (Table0 in the form?

The Articles (Table) (typo with the closing bracket, missed the shift key) was there for reference, probably should have deleted it. Moving forward you’d want to use the Articles (Link) because it enables a little more precise referencing of the individual records.

When I run the script, the new records are not added in the Keywords form (only in the Articles form)
Did I do something stupid or change something without realizing it?

The script as written was targeted for the Articles (Table) field, if you’re using the Articles (Link) then all you need to do is create a new record in the Keywords with the value of the keyword that you’ve added then Tap Forms will automatically link everything together for you.

For the Magazine Month field (calculation type), I cannot display it in a date format type MMMM YYYY

Ok, for the Magazine Month field in the Articles form, go into the calculation settings and set the Result Type option on the left hand side under the field list to Date. That will tell Tap Forms to treat it like a date value rather than a text or number value. The default result type for calculation fields are numbers and the default result type for script fields are text.

When it’s set to Date then you can use the other field settings to control how the date field displays.

[pierrot_rennes]

Hi Sam,

Really thank you for your help.
I did some tests and I took a good look at the operation of the fields and their properties with the links to the other forms.

I don’t know if I’m right but here’s how I would like it to work

It seems easier and more logical to fill in the information from the Magazines form.
I fill in the Magazine, Number, date fields then in the Articles table (Link), I list all the articles that interest me with the key batch and the corresponding subject.

Ideally, this Magazines form automatically populates the Keywords form by filling in the Articles table.
This Articles table would include 4 fields: Magazines, Number, Date, Subject
These fields come from the Magazines form.

Is it possible to automatically populate a Form B table from a Form A table?

The Articles Form is not useful to me because it only lists the keywords and it is not readable.
The idea is really to easily and quickly see which magazines have covered a specific keyword.

As attachments, I have given you two screen copies to explain my point.

Don’t spend too much time on my case either ;-)), I’m looking for something easy to use.

Thank you sincerely for your help and advice.

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

With the link you can absolutely fill it in from the Magazines form, that makes complete sense because when you add from the Magazines form, it autolinks the Magazine to the article for you similar to how the Table field operates.

The Articles form is just a more accessible version of the table field you created, I think under the hood Brendan modelled them the same way, it’s just that you don’t see the form as the field itself is the form. You could hide the Articles form if you wanted so you’d not see it in the forms list but would still be able to interact with it. I personally like having them all available but the option is there.

We can automate a lot of things using the calculation fields to pull data from the parent record. Tap Forms can’t give you a table view that has fields from either two different forms or fields from the parent form and rows in a table field. That’s in part where that Articles form comes in because we can reference that from other forms (Keywords in this case) and since it is linked to the Magazine form, we can use calculation fields in the Articles form to automatically replicate values from the parent Magazine form. The other advantage is that Tap Forms’ Link to Form JOIN field works on the form making it a little easier.

If you only want to link to the magazine plus magazine month and don’t need the article name or page details (essentially anything in your articles table or the articles form) in your keyword form, then you could change the Articles form (and link) back into just a simple table. I might have misunderstood along the way but I thought it was useful to see the article and page details for the keyword as well. That’s what a lot of this is predicated on is pulling all of those fields together into the keyword to make a table.

Looking at your second screenshot, if the Magazines link isn’t useful then you can safely remove it and for the articles link in your second screenshot if you click on the X button on the far right you can hide fields and reorder them (this also works in the multicolumn list view as well). Getting magazine month and number should be a calculation field that populates the value from the parent magazine form, this is just a matter of setting the fields up and making sure they have the right type (date for the month field and number for the number field [number should be the default]). If the calculation field is misbehaving, jump to the Articles form and click on the refresh icon on the bottom of the list view and it should recalculate the fields to update to the right value.

The challenge with automatically creating a keyword if one doesn’t exist is that we need to scan the keywords form to find what keywords exist and do an insert if one doesn’t exist. If I was using something like MySQL, it has functionality that is optimised to make this mostly cheap through it’s indexing system but Tap Forms isn’t built with a columnar index structure but is a document store. That means we need to build the index ourselves. Doing this each time you edit the keywords field is probably a little intensive and would slow Tap Forms down as your database scaled in keywords. I’m also going to make an assumption that you’re going to hit a threshold where adding a new keyword is a rare event rather than a common one, as it will be today. Given both of those, I went to create a form script that handles it. You could also modify it to be a script field (might even work as is) but again that would be a little disruptive to editing that might not be appreciated.

Here is a quick little form script that runs on the currently selected magazine record, scans the linked articles and checks the keyword table to see if it exists and creates a new keyword record if it doesn’t exist. It’s actually a slightly modified version of the earlier script, just without the link creation steps:

function Update_Keywords() {
	// This is the other form that has our keywords in it.
	let keywordForm = document.getFormNamed('Keywords');
	
	// This is the field ID in the "keywords" form for the link to field to Magazines.
	let magazines_id = 'fld-b4724a28d4094b3faa9910f18374588f';

	// This is the field ID in the "keywords" form for the keyword field.
	let keywords_keyword_id = 'fld-ebaf7ffa3d484834bdcc34d3ffb9c5f2';
	
	// This is the field ID in the "magazine" form for the keyword field in the articles table.
	let keyword_id = 'fld-a75febca3ee54d2d9d77b8c176ac08db';

	// Articles link field ID
	let articles_link_id = 'fld-9af3b672710949d8a96591e23ba5466b';

	// This is to store a map of keyword to keyword record.
	let kwRecords = {};

	// Build the map of keyword to keyword record (cache if you will).
	console.log("Finding keyword records...");
	for (let keywordRecord of keywordForm.getRecords())
	{
		console.log("Adding keyword: " + keywordRecord.getFieldValue(keywords_keyword_id));
		kwRecords[keywordRecord.getFieldValue(keywords_keyword_id)] = keywordRecord;
	}
	console.log("Completed finding keyword records.");

	// Iterate over every record in this form...
	for (let sourceRecord of record.getFieldValue(articles_link_id))
	{
		// Get the value of the keyword field...
		let keyword = sourceRecord.getFieldValue(keyword_id);
			
		// Skip it if it is empty...
		if (!keyword)
		{
			continue;
		}

		// Check if a keyword record exists for it...
		if (!kwRecords[keyword])
		{
			// Create keyword record if it doesn't exist...
			console.log('Creating keyword record');
			kwRecords[keyword] = keywordForm.addNewRecord();
			kwRecords[keyword].setFieldValue(keywords_keyword_id, keyword);
		}	
		
		// Log the keyword we processed, we're done!
		console.log(keyword);
	}
	
	// Save the changes.
	form.saveAllChanges();
}

Update_Keywords();

[pierrot_rennes]

Hi Sam,

Really thank you very much for your help, your explanations and the script !!!
thank you very much and respect for the time you spent on this

I just integrated the new script and I made two or three tests quickly by modifying the properties of some fields as you explained to me
It seems to work fine and in the way that suits me ;-))

I will experiment and test at the field level
And especially to learn the links of forms and controls ;-)

I will also try to be inspired by your script code although the code is not my preference ;-))
Very long practice of IT but rather on the hardware side, network and troubleshooting …

Thanks again for your help
Best

[Sam Moffatt]

Good to hear it helped you out and hopefully also helps out anyone else on the forum that finds it :)

[pierrot_rennes]

Hi Sam,

If you want, you can make it available as a new form
Maybe it will interest someone who hadn’t thought about it before

I was using Ninox Database on iOS for this but the software does not exist on MacOS and above all, it is less convenient than Tap Forms to register data.
I was able to export the data of 400 entries from Ninox to Tap Form with a CSV file.
By renaming the column headers of the Ninox fields in the CSV file, I managed to integrate them into the corresponding fields of the Articles form
Then, in the Magazines form, I was able to retrieve the data in the Articles (Link) table by selecting the existing linked articles.
It worked well and it’s a huge time saver !!!
I did not have to re-enter everything in Tap Form

Thanks again

[Sam Moffatt]

You should do it! It’s your use case and to an extent your solution. You should talk to it, what you wanted to achieve and how it works for you.

Good to hear you were able to get over from Ninox without too many hassles too. Hopefully Tap Forms serves you well :)

[Author]

Posts