Mail Merge not working

[Kimberley Hoffman]

Hi,
I am prepping for an art exhibition with a closing auction. I am using Hahnemühle myartregistry.com to register my artworks. For this purpose I’ve gotten unique holographs for each artwork which are numbered and will also be printing individual certificates.

I thought, hey, this is a great job for Tap Forms. I even watched the mail merge video, because it does apply to my project. I am also using relationships between forms. (That seems to work)

As per the video I have put my fields in “[ ]” closed brackets (no quotation marks). However, when I print the field values are not placed. What is going wrong? Have I got the wrong brackets?

I also am supposed to add the series numbers which would be a from-to number type field. That is a “one to many” field. Perhaps I cannot add these numbers as a series to the certificate the way that I would like. I will need a work around, like selecting the numbers again.

Please look at my screenshots. I hope that I have just made a simple mistake, but have been working over three hours (that I cannot spare) to try to fix this. I have about 30 images to sign, number and catalog. And time is running out.Please help. Thanks.

Regards,
Kimberley

Attachments:

You must be logged in to view attached files.

[Brendan]

Hi Kimberly,

The only one I see not working is [Edition Nr.]. But in your fields list you have it defined as Edition Nummer. All the others appeared to be substituted properly when you print.

For the series numbers, you won’t be able to reference those in your record directly. Since it’s a one-to-many, is it ALL of the series numbers that you want to display that are related to the parent record? You would most likely need a script field to extract the data from the Link to Form field to get the list of series numbers to include in the report.

Thanks,

Brendan

• This reply was modified 2 years, 7 months ago by Brendan.

[Kimberley Hoffman]

Hi Brendan,
Thanks for getting back to me so fast. I had originally used “Edition Nr.” but changed it, forgetting to correct it in the layout.

For the series numbers, I need to display

A certain certificate number that I get from the hologram label (Zertifikatsnummer)
A specific range of certificate numbers, i.e 000019 to 000023 (or however high the edition is), but not all the certificate numbers that I enter in the second form

I am not quite sure what kind of script I would need to apply to those two.(get records?) (My head begins to send panicky smoke signals when someone says “script”.)
Thanks
Regards,
Kimberley

• This reply was modified 2 years, 7 months ago by Kimberley Hoffman.

[Kimberley Hoffman]

I was looking through the script forum. This seems close but it isn’t exact: https://www.tapforms.com/forums/topic/how-to-get-and-add-data-from-other-form/

I got it up to here:
function Get_Zertifikatsnummer() {

// Get the Zertificatsnummer from the Seriennummern form

var Seriennummern_form_id = ‘fld-123…’;
var Seriennummern_form = record.getFieldValue(Seriennummern_form_id);
var name_id = ‘fld-abc…’;
var name_value = customers_form.getFieldValue(name_id);
}

// Get the Print Registry, create a new record in it and set the value in a field

var Print_Registry_Form = document.getFormNamed(“Get Zertificatesnummer”)
var

Am I getting close? The top part seems to turn up without error, but the field is empty. Or am I looking in the wrong direction?
Thanks
Kimberley

• This reply was modified 2 years, 7 months ago by Kimberley Hoffman.
• This reply was modified 2 years, 7 months ago by Kimberley Hoffman.

[Kimberley Hoffman]

I’ll try this again.
I’ve looked here: https://www.tapforms.com/forums/topic/how-to-get-and-add-data-from-other-form/

and tried to emulate it, but I am stuck, kind of like trying my best to speak Russian, when I only know the words “da” and “nyet”.

This is what I have tried:

function Get_Zertifikatsnummer() {

// Get the Zertificatsnummer from the Seriennummern form

var Seriennummern_form_id = ‘fld-123…’;
var Seriennummern_form = record.getFieldValue(Seriennummern_form_id);
var name_id = ‘fld-abc…’;
var name_value = customers_form.getFieldValue(name_id);
}

// Get the Print Registry, create a new record in it and set the value in a field

var Print_Registry_Form = document.getFormNamed(“Print Registry”)
var name_id = ‘fld-xyz…’;
var seriennummer_record = get_zertifikatsnummer.addNewRecord
get_zertifikatsnummer.setFieldValue(field_id, name_value);

form.saveAllChanges()

}

This is the the return that I get:
13.09.21, 20:20:23 / Print Registry / Get Zertifikatsnummer
Get Zertifikatsnummer: SyntaxError: Parser error, line:20

This one is just for getting the certificate number.
If I can manage this than I could possibly graduate to getting the range of certificate numbers

But let’s bake some small rolls first. :)

Kind regards,
Kimberley

[Sam Moffatt]

It looks like you have one extra }, there is one after the name_value line and then one at the end (I’d guess line 20). One of the conventions in programming is indentation and essentially any time you do an { character, indent the next line with a tab or some spaces and then you remove it for each } you see. That makes it easy to spot when they aren’t balanced.

There is also a reference to customers_form that I don’t think exists yet so once you solve the parse error you’ll likely have to fix that, I’m guessing that’s supposed to be Seriennummern_form.

This line:

get_zertifikatsnummer.setFieldValue(field_id, name_value);

Should probably not be get_zertifikatsnummer but seriennummer_record which is the record you just created.

[Sam Moffatt]

As an aside, it might be easier to just have a text field for the series number and manage it manually, especially if sometimes it comes from one place and sometimes another. If you’re only excluding numbers within a range (e.g. 20, 21 and 22 in the example), then you could still script it though it’s a little more work to mask those numbers but not impossible.

One other thought is to copy/paste through an online Javascript beautifier like beautifier.io. Attached is what it looked at a first pass (I also had to fix the smart quotes). The indentation change makes it clear where the problem is and taking it out results in everything being indented properly. If I move over to jslint.com I can paste it and it’ll give me a slightly different error message:

1. [JSLint was unable to finish] Expected ‘(end)’ and instead saw ‘}’.
}

Here is the code with a few more changes, but it still won’t work:

function Get_Zertifikatsnummer() {

    // Get the Zertificatsnummer from the Seriennummern form

    var Seriennummern_form_id = "fld-123…";
    var Seriennummern_form = record.getFieldValue(Seriennummern_form_id);
    var name_id = "fld-abc…";
    var name_value = Seriennummern_form.getFieldValue(name_id);

    // Get the Print Registry, create a new record in it and set the value in a field

    var get_zertifikatsnummer = document.getFormNamed("Print Registry")
    var name_id = "fld-xyz…";
    var seriennummer_record = get_zertifikatsnummer.addNewRecord();
    seriennummer_record.setFieldValue(field_id, name_value);

    form.saveAllChanges()

}

Why won’t it work? Well this line:

    var Seriennummern_form = record.getFieldValue(Seriennummern_form_id);

This line doesn’t return a Tap Forms record but returns an array of records, perhaps think of it like a bag. You need to loop over it to get a scalar or single value. Now there is a kind of quick way that will get you a comma separated list of fields, it looks like this:

    var name_value = record.getFieldValue(Seriennummern_form_id).map((rec) => rec.getFieldValue(name_id)).join(", ");

This is a bit of black magic and is a little unreadable but unpacking:

• record.getFieldValue(Seriennummern_form_id) will give us all of the child records, I’m assuming this is your link to the serial number form.
• map is a special function that says take this value (in case rec which is technically a TFFormEntry) and then replace it with the result of the other command (in this case rec.getFieldValue(name_id) which I’m guessing is the serial number). So now instead of it all being child records, it’s the serial numbers.
• Now that we’re dealing with just the serial numbers in the array, we can do join(", ") to make them into a comma separated list (technically it’s comma and then space since it’s ,).

I suspect that’s what you’re after and will give you a quick start for that field. You could still loop to replace contiguous numbers but I feel doing some of it by hand might be ok whilst you learn.

Here’s my final script with a few extra changes. I ran this through JSLint and fixed each of it’s complaints (e.g. there wasn’t a field_id and there was two name_id fields, so I renamed the second one to be field_id). I also added a call to the function, otherwise the script will do nothing (e.g. the Get_Zertifikatsnummer(); line at the end).

function Get_Zertifikatsnummer() {

    // Get the Zertificatsnummer from the Seriennummern form
    var Seriennummern_form_id = "fld-123…";
    var name_id = "fld-abc…";
    var name_value = record.getFieldValue(Seriennummern_form_id)
        .map((rec) => rec.getFieldValue(name_id))
        .join(", ");

    // Get the Print Registry, create a new record in it and set the value in a field
    var get_zertifikatsnummer = document.getFormNamed("Print Registry");
    var field_id = "fld-xyz…";
    var seriennummer_record = get_zertifikatsnummer.addNewRecord();
    seriennummer_record.setFieldValue(field_id, name_value);

    form.saveAllChanges();
}

Get_Zertifikatsnummer();

One other note, if there is a field in this form that links to the Print Registry then you should use record.addNewRecordToField(link_field_id) which will not only create a new record in the target form for you but also create the linking metadata as well.

Attachments:

You must be logged in to view attached files.

[Kimberley Hoffman]

(Edit: I posted this a little later than I wrote it because the doorbell rang. And now I see you elaborated)

Thanks Sam,
The error has disappeared, but no certificate number appears. The field remains empty. The customers_form mistake was probably typing exactly what I was reading and forgetting to replace it with a line.

My brother says the hardest part about coding is knowing what you result you want to appear.
I beg to differ. I know what I want to appear, but don’t know the magic formula for that yet.

So I will repeat my (thought) process here, maybe you will see my error:
1. I import a list of 6 digit series numbers as a .csv file into the file “Seriennummern”. The .csv-file is exported from Numbers, so I reduce the likelihood of typos.You can easily create an ascending digit column in Numbers by typing in the first two fields, marking them and dragging the curser down the column.
2. I input information in the print registry, like Title, format, etc.
3. I choose the “Zertifikatsnummer”. I have this as a one to many field
4. The series of the certificate numbers, “Fortlaufende Hologrammnummern der Serie” is also a one to many field. Explanation: I must express in the certificate print form how many of Print X is made and what series numbers this print edition will have.
5. I will print out the certificate, affix the appropriate hologram to both the print and the certificate

The text on the certificate (translated by machine here just for general understanding) will read:

This print and certificate are each identified by a hologram bearing the unique assigned number [certificate number].

This fine art print is the [edition number] of a limited edition of [edition number of prints] prints signed by the artist (hologram numbers [consecutive hologram numbers of the series]).

The print was created on [print creation date] with [ink type] on [paper].

Wolfenbüttel, the [signing date].

Kimberley Hoffman
Illustrator|USA
http://www.hoffman-illustrates.com
info@hoffman-illustrates.com

• This reply was modified 2 years, 7 months ago by Kimberley Hoffman.

[Kimberley Hoffman]

Hi Sam,

Thanks for going through all that effort. I am really trying to understand what you are explaining.

I think what you are saying is that it is just better to copy and paste the serial number and the range of serial numbers into a document until I have finished my baby steps. Right?

Kindest regards,
Kimberley

(But I still think that having the certificate databank in Tapforms is the best solution for my records, even if I cannot automatically output a certificate. I can add the images and still have a record of the series of certificate numbers)

• This reply was modified 2 years, 7 months ago by Kimberley Hoffman.

[Kimberley Hoffman]

So, I have taken a work-around, because it is impossible to understand/learn coding on the fly for me. I have added some extra fields to copy and paste my numbers into. That does let me do a simple range.

It isn’t an elegant solution, but it will work for me. Thanks for all your efforts on my behalf.

Kindest regards,
Kimberley

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

My brother says the hardest part about coding is knowing what you result you want to appear.
I beg to differ. I know what I want to appear, but don’t know the magic formula for that yet.

Once you know how to code I can see the first taking over but you’ve got to learn how to put the code together. That takes some time to figure out the pieces.

So I will repeat my (thought) process here, maybe you will see my error:
1. I import a list of 6 digit series numbers as a .csv file into the file “Seriennummern”. The .csv-file is exported from Numbers, so I reduce the likelihood of typos.You can easily create an ascending digit column in Numbers by typing in the first two fields, marking them and dragging the curser down the column.

At some point this should become a script you run in TF to generate the entries so you don’t have to import a CSV file just to generate a series.

2. I input information in the print registry, like Title, format, etc.
3. I choose the “Zertifikatsnummer”. I have this as a one to many field
4. The series of the certificate numbers, “Fortlaufende Hologrammnummern der Serie” is also a one to many field. Explanation: I must express in the certificate print form how many of Print X is made and what series numbers this print edition will have.
5. I will print out the certificate, affix the appropriate hologram to both the print and the certificate

Are both of these link to form fields (“Zertifikatsnummer” and the “Hologrammnummern”) linking to the same “Seriennummern” form? Or are they different series?

I think what you are saying is that it is just better to copy and paste the serial number and the range of serial numbers into a document until I have finished my baby steps. Right?

There was a quote I heard the other day that “You need to get things done before you get bored with them” and I’d substitute “bored” with “frustrated” in this case. There is a pragmatic path to getting unblocked and the long term process improvements. Learning when you’re stressed is a path to more stress :) Solve the initial problem and come back to making it better :)

(But I still think that having the certificate databank in Tapforms is the best solution for my records, even if I cannot automatically output a certificate. I can add the images and still have a record of the series of certificate numbers)
…
So, I have taken a work-around, because it is impossible to understand/learn coding on the fly for me. I have added some extra fields to copy and paste my numbers into. That does let me do a simple range.

It isn’t an elegant solution, but it will work for me. Thanks for all your efforts on my behalf.

It’s not an elegant solution but it’s something that enables us to have something that works and we can go from there. On the script talk basic information on scripting topic I had started to add some logging. If you run this in the script editor, you should see some output on the console log area on the right:

function Get_Zertifikatsnummer() {

    // Get the Zertificatsnummer from the Seriennummern form
    var Seriennummern_form_id = "fld-123…";
    var Seriennummern_form = record.getFieldValue(Seriennummern_form_id);
    console.log(Seriennummern_form);
    var name_id = 'fld-abc…';
    var name_value = Seriennummern_form
        .map((rec) => rec.getFieldValue(name_id))
        .join(", ");

    console.log(name_value);

    // Get the Print Registry, create a new record in it and set the value in a field
    var get_zertifikatsnummer = document.getFormNamed("Print Registry");
    var field_id = "fld-xyz…";
    var seriennummer_record = get_zertifikatsnummer.addNewRecord();
    console.log(seriennummer_record.getUrl());
    seriennummer_record.setFieldValue(field_id, name_value);

    form.saveAllChanges();
}

Get_Zertifikatsnummer();

This should output some information to help understand the internal state of the script and why things are empty…or where they start being empty :)

[Kimberley Hoffman]

Hi Sam,

You are right in learning while stressed is not very effective. I will have to start learning in order to fully explore Tap Forms. I think that once finished with my current search and find book I will see if I can take a course locally for the basics. I need to understand the vocabulary and expressions. I look at the things you wrote and feel an immense overwhelm, just because I need to understand what is going on. Thank you.
Kindest regards,
Kimberley

[Sam Moffatt]

No worries, good luck getting it all sorted :)

[Author]

Posts