Calculated field option within tablefield

[RonP]

Hi,
What do I mis
I try to generate a URL, based on Zip code and House number (Based on that you should go to a Dropbox directory)
The given SQL-statement goes well, except that all the records in the table gets the SAME URL content.

Code: Select all

SELECT CONCAT("<a href=""https://www.dropbox.com/home/Energiecoach/DossierBewoners/",
              `Aanvragen`.`Postcode`,
              `Aanvragen`.`Huisnummer`,
              `Aanvragen`.`Toevoeging`,
              """",
              "  target=""_blank",
              " rel=""noreferrer noopener"">",
              `Aanvragen`.`Postcode`,
              `Aanvragen`.`Huisnummer`,
              `Aanvragen`.`Toevoeging`,
              "</a>"
) AS url from `aanvragen`

When I expand this with a where clause, then none of the NEWLY created records receive the wanted URL, and changed Zip code / House number doesn't result in a new URL.

Code: Select all

where `aanvragen`.`Id` = %ID%

Thanks in advance

[pbottcher]

Hi Ron,

did you check that you use Id as the primary key in your AppGini definition?

Otherwise you may try

Code: Select all

where %PKFIELD% = %ID%

[RonP]

Hi Pböttcher.
Thank you for pointing to a solution, however
I can’t get it done.
New records don't get the expected calculated result
Changed records don't get a changed calculated result
Ron

(I'm using V22R13 revision 1291)

[RonP]

Subject can be closed !
There seemed to be a SQL-statement conflict, debugging and it works now as expected

[RonP]

Well.....

It turned out to be that the used concatination in the SQL-statemnet gives a problem.

The case:
I like to concatenate 3 fields and work to an url.
In fact it works well, however whenever the last field is empty (Toevoeging) the URL isn't created.
The field defenition in the database:

The defenition in AppGini: (Admin Area:View rebuild fields)

Code: Select all

Toevoeging	VARCHAR(40) NULL	VARCHAR(40)

The content of the calculated field is:

Code: Select all

SELECT CONCAT("<a href=""https://www.dropbox.com/home/Energiecoach/DossierBewoners/",
              UPPER(`aanvragen`.`Postcode`),
              UPPER(`aanvragen`.`Huisnummer`),
              UPPER(`aanvragen`.`Toevoeging`),
              """",
              "  target=""_blank",
              " rel=""noreferrer noopener"">",
              UPPER(`aanvragen`.`Postcode`),
              UPPER(`aanvragen`.`Huisnummer`),
              UPPER(`aanvragen`.`Toevoeging`),
              "</a>"
) AS url from `aanvragen`
where `aanvragen`.`Id` = %ID%

The Resultfield for calculated field is: `Dropbox-link` (Admin Area:View: rebuild fields)

Code: Select all

Link_Dropbox	VARCHAR(255) NULL	VARCHAR(255)

Generated HTML when filed `Toevoeging`is filled:

Code: Select all

<!-- Field: Link Dropbox -->
				<div class="form-group aanvragen-Link_Dropbox">
					<hr class="hidden-md hidden-lg">
					<label class="control-label col-lg-3">Link Dropbox <i class="glyphicon glyphicon-info-sign text-info" data-toggle="collapse" data-target="#Link_Dropbox-description"></i></label>
					<div class="col-lg-9">
						<span class="help-block bspacer-xs collapse" id="Link_Dropbox-description"><div class="alert alert-info bspacer-xs">Wordt gegenereeerd uit: Postcode, Huisnummer, Toevoeging</div></span>
						<div class="form-control-static" id="Link_Dropbox"><a href="https://www.dropbox.com/home/Energiecoach/DossierBewoners/1852AM140D" target="_blank rel=">1852AM140D</a></div>
					</div>
				</div>

So whenever field `Toevoeging`has a vlue an URL has been created

<a href="https://www.dropbox.com/home/Energiecoa ... 1852AM140D" target="_blank rel=">1852AM140D</a>

Generated HTML when fieldd `Toevoeging` is not filled out:

Code: Select all

<!-- Field: Link Dropbox -->
				<div class="form-group aanvragen-Link_Dropbox">
					<hr class="hidden-md hidden-lg">
					<label class="control-label col-lg-3">Link Dropbox <i class="glyphicon glyphicon-info-sign text-info" data-toggle="collapse" data-target="#Link_Dropbox-description"></i></label>
					<div class="col-lg-9">
						<span class="help-block bspacer-xs collapse" id="Link_Dropbox-description"><div class="alert alert-info bspacer-xs">Wordt gegenereeerd uit: Postcode, Huisnummer, Toevoeging</div></span>
						<div class="form-control-static" id="Link_Dropbox"></div>
					</div>
				</div>

So whenever field `Toevoeging` has NO value an URL has NOT been created

[pbottcher]

Hi again,

try

CONCAT_WS("","

instead of

CONCAT("

[RonP]

That did the trick
Thank you.
Ron