Template logic issue problem

[JamesWB]

I struggle a bit with template code, so apologies if this is in fact simple!

I have a template that requires either a block of text or selected record to be written, depending on a text value stored in the record.

The structure is that I have a Machine table and a related Hazard table. Hazard contains a text column, 'Regulation'.

Each block in the template is either some standard text or for a given regulation, I want to print out all Hazards containing that Regulation, in the relevant block. The template prints out values for Machine table and related values from Hazard table.

Eg, 

Block Regulation Four
Standard text

Block Regulation Five
Hazard 1 containing Regulation 5
Hazard 2 containing Regulation 5

I understand how to test for conditions, but in the example above, Block Regulation Five presumably requires that within Start...End I insert an If condition to test if the string matches? Is that right, can I do that?

I also thought of having a slice for each Regulation number and then calling the relevant slice, but I still don't know how to test for that.

Grateful for any help!

thanks

James

[SkrOYC]

You can use Template If to deal with selecting if you are going to need just a field or a Start: with list of children records.
You can just use an expression that returns a list of values from your children without using Start:, with something like List dereference.
You have more than just one option, and the best solution would vary depending on your schema and the template file being used

Use If expressions in templates - AppSheet Help
Use Start expressions in templates - AppSheet Help
Build list dereferences - AppSheet Help

[JamesWB]

Thanks, I've studied the If and Start pages. I've tried to implement this in the template:

<<Start:SELECT([Related Hazard][Regulations], [Regulations] = "15 - Stop Controls")>>

In a code block between If and EndIf but it doesn't work, the template doesn't execute. The error messages are no help.

I don't understand the list dereference page. I'd need to see some examples to understand it better I think.

What I'm trying to do logically is:

Test if a given regulation is found in the Hazard list.
If it is, print the Hazards that match that Regulation.
If not, print some standard text.

Can you please indicate to me what the structure of that should be, eg,

If statement 
Select xxx
End If

?

Does Select only work if all parts are inside a table and connected? I've noticed that it seems that you can't have a Select statement, then some text in the template, then the fields, then the Ends?

Eg, I want to do this:

Hazard Title
<<Start:SELECT([Related Hazard][Regulations], [Regulations] = "15 - Stop Controls")>>
... print out the Hazards...
<<End>>

Hazard Title
<<Start:SELECT([Related Hazard][Regulations], [Regulations] NOT "15 - Stop Controls")>>
... print out some standard text...
<<End>>

(sorry for the edits, struggling with the pathetic little editing window)

[JamesWB]

I thought this might work, but the template doesn't execute.

<<Start: [Related Hazards]>><<IF: CONTAINS("15", LEFT([Regulations], 2))>>

HazardCreation	Regulations	Brief Hazard Description	Image	Factor 1
<<Start: [Related Hazards]>><<[HazardCreation]>>	<<[Regulations]>>	<<[Brief Hazard Description]>>	<<[Image]>>	<<[Factor 1]>><<End>>

<<EndIf>>
<<End>>

[JamesWB]

And this also doesn't work.

<<Start: [Related Hazards]>>

<<IF: CONTAINS("15", LEFT([Regulations], 2))>>

HazardCreation	Regulations	Brief Hazard Description	Image	Factor 1
<<[HazardCreation]>>	<<[Regulations]>>	<<[Brief Hazard Description]>>	<<[Image]>>	<<[Factor 1]>>

<<EndIf>>
<<End>>

[SkrOYC]

---

@JamesWB wrote:

<<Start:SELECT([Related Hazard][Regulations], [Regulations] = "15 - Stop Controls")>>

---

Start: expressions requires a list of ref values. At first glance, it seems you are trying to call a column that's not the key, because you are then comparing the same column with some text.

---

@JamesWB wrote:

Hazard Title
<<Start:SELECT([Related Hazard][Regulations], [Regulations] = "15 - Stop Controls")>>
... print out the Hazards...
<<End>>

Hazard Title
<<Start:SELECT([Related Hazard][Regulations], [Regulations] NOT "15 - Stop Controls")>>
... print out some standard text...
<<End>>

---

<<Start:[Related Hazard][KeyColumnFromHazard]>>

<<If:SomeExpressionInvolvingAColumnFromWithinHazard_CheckIfTrue>>
Text if true
<<EndIF>>

<<If:SomeExpressionInvolvingAColumnFromWithinHazard_MaybeJustWrapWithNOT()>>
Text if it's not
<<EndIf>>

<<End>>

I cannot provide too much info without knowing your schema

[JamesWB]

Thanks, this is useful.

My schema is simple - table Machine has a child table Hazard. There is a MachineID in Hazard and a HazardID in Hazard.

The template is to print values from Machine and related values from Hazard.

Your example logic isn't quite what I need, as I need to print a table of columns from Hazard in each section. That's where I'm struggling. The logic I actually need is:

<<Start:[Related Hazard][Regulation]>>

<<IF: CONTAINS("15", LEFT([Regulations], 2))>>
.... print 5 table columns from Hazard here....
<<EndIF>>

<<IF: NOT CONTAINS("15", LEFT([Regulations], 2))>>
... print some other text...
<<EndIf>>

<<End>>

but this doesn't execute as above.

[JamesWB]

This is the full template code I'm attempting (which fails):

<<Start:[Related Hazard][Regulation]>>

<<IF: CONTAINS("15", LEFT([Regulations], 2))>>

HazardCreation	Regulations	Brief Hazard Description	Image	Factor 1
<<[HazardCreation]>>	<<[Regulations]>>	<<[Brief Hazard Description]>>	<<[Image]>>	<<[Factor 1]>>

<<EndIF>>

<<IF: NOT CONTAINS("15", LEFT([Regulations], 2))>>
Everything is fine.
<<EndIf>>

<<End>>

[JamesWB]

This doesn't work either, which points to not being able to use START outside a table?

<<Start:[Related Hazard][Regulations]>>

<<IF: IN(LEFT[Regulations], 2), "15")

HazardCreation	Regulations	Brief Hazard Description	Image	Factor 1
<<[HazardCreation]>>	<<[Regulations]>>	<<[Brief Hazard Description]>>	<<[Image]>>	<<[Factor 1]>>

<<EndIF>><<End>>

[SkrOYC]

Some of your code has errors.
For example, CONTAINS() is being used as if there is just a direct comparison, better use "="
NOT should wrap the whole expression NOT(Expression)

NOT() - AppSheet Help

[JamesWB]

OK, I get the NOT bit, thanks.

What should this expression be then if it uses "="?

<<IF: CONTAINS("15", LEFT([Regulations], 2))>>

[SkrOYC]

<<IF:LEFT([Regulations], 2)="15>>

[JamesWB]

Thanks. I tried that and a NOT, the template doesn't execute.

<<Start:[Related Hazards]>>

<<IF:LEFT([Regulations], 2)="15">>

HazardCreation	Regulations	Brief Hazard Description	Image	Factor 1
<<[HazardCreation]>>	<<[Regulations]>>	<<[Brief Hazard Description]>>	<<[Image]>>	<<[Factor 1]>>

<<EndIf>>
<<End>>

<<Start:[Related Hazards]>>

<<IF:NOT(LEFT([Regulations], 2)="15")>>

Some other text

 <<EndIf>>
<<End>>

[JamesWB]

This doesn't work either. It doesn't like having a conditional within a Start.

<<Start:[Related Hazards]>>

<<IF: LEFT([Regulations], 2)="15">>

HazardCreation	Regulations	Brief Hazard Description	Image	Factor 1
<<[HazardCreation]>>	<<[Regulations]>>	<<[Brief Hazard Description]>>	<<[Image]>>	<<[Factor 1]>>

<<EndIf>>
<<End>>

[SkrOYC]

The way you have layed out things is not compatible with the examples I provided earlier.
The Template If should either wrap the entire Start: or be inside a single record iteration, but you are covering all of the fields for your records with the template if.

I'm sorry if I let you to that confusion.

It would be better if you actually tell us the real use case and provide us with the database design

[JamesWB]

Yes, the problem is though that Regulations is in the table Hazard, which is a child table of Machine and the template needs to call Machine.

The use case is  that there are Hazards belonging to each Machine. Within a Hazard, there is a Regulation.

Machine table:

Site

MachineID

Created By

Machine Name

Image

Serial

Location

Hazard table:

Machine

HazardID

HazardCreation

Regulations

In the template I want to have two choices. If Regulations field is a given value like "15", I want to write out Hazard records containing that value in a given section of the template, Section 15.

If there are no Hazard records containing "15" in the Regulations column, Section 15 in the template should instead display some standard text.

[SkrOYC]

Please provide the column type for each one of the involved fields