Formatting a Reference row

Dan_Oliphant · 04-06-2020 12:52 PM

How do we format a reference row. I have attached a video to better explain it.

WillowMobileSys

You would use Format Rules to apply the formatting.

Below is an example of how I formatted a Business customer to make it stand out from a Residential customer.

My Dropdown look like this

The Format Rule was defined like this

You can set different text attributes as well

Keep in mind that format rules apply to everywhere (unless specified)

IF I didn’t want my Format Rule to apply to the view above, I could have added into the Format Rule expression something like:

CONTEXT("View") <> "MyViewName"

Dan_Oliphant

i am sorry, but this doesn’t solve my problem. I would like the formatting to be on the words like you have it. but your formatting is all happening in the same sheet. I need my formatting to be based on the reference sheet UNIQUE ID

WillowMobileSys

If I am understanding correctly…

My Customers dropdown IS based on a UNIQUEID() column named Customer ID. The Format Rule is placed against the column used as the Label for that dropdown. In my case Name is that label column.

If you create a Format Rule on the column that has the “words”, you will be fine!

By the way, I realized a simpler way to have the Format Rule apply ONLY towards the dropdown. Use this in the expression:

CONTEXT("ViewType") = "Form"

Dan_Oliphant

Trying to better explain it… I need an expression
If the child sheet (in my case Origin Draw) column [Room] which is a Ref, is equal to ANY of the [Unique ID] in the parent sheet (in my case Origin) - then format those

WillowMobileSys

Got it! What I explained before is how you apply the Format Rule to that drop down.

What you are referring to now is help with the expression that goes into that Format Rule.

For now, I recommend getting the Format Rule implemented in the way you want it to look. In the meantime, I’ll watch your video again and see if I can help with the expression to place in that rule so the format is only applied to the rows you wish.

I’ll be back in a few minutes.

WillowMobileSys

I am not clear on what value [Unique ID] contains?

If I follow your written explanation then in the Format Rule you can have the expression like this:

AND(
    CONTEXT("ViewType") = "Form", 
    IN([Room], SELECT(Origin[Unique ID], true, true)
)

In the SELECT the “true, true” part just means give me all unique ID’s - meaning no dups.

So, basically this is saying “if its a Form and the Room ID is in the list of Unique ID’s from the table Origin” then apply the Format Rule.

Dan_Oliphant

Thanks.

Dan_Oliphant

That is exactly right, but its still not working…

Dan_Oliphant

the unique ID value is just a random 8 digits/letters

WillowMobileSys

What I meant is…Is this [Unique ID] the Room ID value? How does it get assigned?

Dan_Oliphant

it just formats them all

Dan_Oliphant

its weird, in the sheet, when I do a Vlookup of those IDs it says it doesnt see them, but they are clearly in both sheets

Dan_Oliphant

I figured it out with a little tweaking. Your expression was super helpful! Thanks so much.

I do have one last issue, then the app is finished. How can you set an initial value of a form, for the Ref column. Is it some kind of lookup?

WillowMobileSys

You can use the Initial Value property of the Ref column. Ref columns MUST be assigned the row ID.

So yes, you can use the LOOKUP() function with the row ID as the return value and is probably the easiest if you can find your row with just one criterion.

If you need to find your row with multiple criteria, you would use a SELECT() to select the row ID. However since this returns that ID as a LIST, you then need to wrap the SELECT() with an ANY() function to return just the row ID value. It would be like this:

ANY(SELECT(Table[ID], <<place select criteria here>>))