Base reports with photos

[rotormansa]

I’m trying to configure a report from a query. First things first, my query I’m trying to create has about 18 fields, of these fields four are photos (jpeg) of certification classes. In my query I’d like to have a “Y” or some designation indicating the record has done the certification. For those that have no certification form that field is blank on the database entry form.
This is a split database. This is on a Windows platform:
Version: 5.3.3.2 (x64)
Build ID: 3d9a8b4b4e538a85e0782bd6c2d430bafe583448
CPU Threads: 4; OS Version: Windows 6.1; UI Render: default; Layout Engine: new;
Locale: en-US (en_US); Calc: group

Update:
I’ve been able to create a report that is almost what I need. Attached is a screen shot of two records from the report: example.jpg
Notice the IS-100, IS200, etc. fields. In the first record this person hasn’t completed the task and received the credentials so in the master form entry there is no data for these fields (blank). In the second record this person has completed those tasks and the location of the file, which is a pdf file, is input into the entry form and stored in the database. My query I use to run the report filters on fields being blank or not and when the report is run the picture is what I get. It’s almost what I need, rather than the file location as is shown I’d like the report or the query to display a Y or 1 or some other short designator rather than what is shown in the report. Is that possible to do? I’ve tried various functions and none seem to work.

Final Update: Thanks for suggesting of adding another field to indicate if the certification form was done by the member (record). That was the easiest thing to do. I tried many different filtering and test criteria and all failed so finally added the extra field of “Y” or “N” and filtering became so much easier.
Look for my post on reports with filter criteria.

[Ratslinger]

Are the photos Blob fields or links to the images? Printing Blob fields from a split DB doesn’t work. Also, it is not clear at to what your question is getting at. Are you having trouble with the query or the report? What specifically is the problem? The only thing stated is that you have a query with fields and want to add another field. Just add the field. Is it a matter of printing something based upon this “designation”?

[rotormansa]

I’ll try and explain better. I have a form that has entry fields for pdf files that are certificates that some records have earn. I am able to put those into the form fine. When I go review records using the forms, the correct pdfs are shown in the form.

[rotormansa]

Now I’d like to create a report that shows the records (people) but the fields for certificates I’d like to just have a Y or some other indicator stating the file is present (the file is the pdf file) and if it isn’t in the record then a N or blank. I thought a query would be the best place to start. My query shows nothing in the field for the certification for any records, I would have thought it would show the location where the file is located.

[Ratslinger]

Still not quite clear. You have a form. Base Form? Has entry Fields. Guessing these are on Base form. Captured in a Table? The Certificate itself - Is it in a separate table and if so how is it linked to Customer? “My query shows nothing in the field for the certification for any records” - what is this field? Sorry for questions but your explanation doesn’t provide much in the way of the Base structure you have and I am just trying to get an image to provide answer.

[Ratslinger]

I have already given the answer. Use the second CASEWHEN. Replace the printing of the location with the new field in query. Syntax:

CASEWHEN("FIELD_TO_CHECK"  <> '', use_this_if_true, use_this_if_false) FIELD_NAME

yours would be

CASEWHEN("IS-100" <> '','Y','') CERTIFICATED

then just use CERTIFICATED for printing instead of location.

[Ratslinger]

You completely missed the contents of my answer. It was not to add a field to a table but simply creating one right in the query.

If future, please do not post as Wiki. This reduces functionality for you and also does not allow you to mark answer as correct.

[Ratslinger]

OK. Just trying to give you some kind of answer. Don’t have much to go on so it’s somewhat of a guess.

If you have a checkbox which indicates “Has Certificate” or similar, you could add this to Select:

CASEWHEN("Has Certificate" = 'TRUE','Y','') CERTIFICATED

or if the field in the Customer record is a link to the certificate:

CASEWHEN("AddressID" <> '','Y','') CERTIFICATED

These, of course, are just guesses of what you may be looking at. Each will output a Y in the CERTIFICATED field of the query output. (you can make fun of the field name I used - I thought it was funny too!).

If this is NOT helpful, more detail will be necessary.