Reference if?

[Cabelo]

Hi all,

WIth the help I am getting here my app is slowly getting where I need it to get to start working
I need to be able to create a condition for a row to be referenced or not to the main table.
I have a lot of hotels and restaurants in their respective tables which are references to the main table of a tour. Each of these restaurants and hotels rows have a True/False column asking if this is the hotel or restaurant being currently used on the tour. I would like TRUE to be a condition for the reference to happen so that only the one I am currently using shows up when visualizing the main tour table.

Is this possible? I tried SHOWIF but I realized it is not what I was looking for, I need more something like a REFERENCEIF if there is such a thing.

Looking forward to learn enough to be helping other people soon…

[LeventK (Levent Kulacoglu)]

@Cabelo
I believe you are trying to filter your reference records as per i.e. if they are active or not. You can do that with an expression in the Valid_if property of your REF column. For example if you only want to list the restaurants which have TRUE value in a particular column i.e. [ACTIVE], then all you need is:

SELECT(Restaurants[KeyColumn],[Active]=TRUE)

This expression will filter away and list all the restaurant records which have TRUE value is [Active] column in your ref dropdown in the main table.

[Steve (Steve Coile)]

If you use a slice as your “main table”, you can use the slice’s row filter to omit the desired rows.

[Cabelo]

@LeventK
I think so, but I couldn’t make it work.
If you look at the screenshot below, you will see my two references to the main table, Meals and Hotels. Right now there are 2 hotels and one restaurant listed.

Out of those 2 hotels we will are currently using one, and for every hotel in that table there is a column called “Current Option?” which is a True/False question. So I would like the “hotels” table and the “meals” table to reference to the main table baaed on the Town column only if “Current Option?” is True. Based on what you suggested me I tried the below, but everything even the hotels and meals which were FALSE for “Current Option?” were still listed.

Thanks again!

[Cabelo]

Maybe I am mixing things up and what I really want is to create a View that relates select data?
Based on the results I got by trying what @LeventK suggested I see that it was filtering what was showing up on the dropdown, so it was a filter for the form itself which was pretty cool to learn how to do and I think I will try to use it somewhere soon
But it made me consider if I am not approaching the issue from the wrong side.

Thanks @Steve I will try understanding the slices a bit more tonight, so far I have been a bit intimidated by it but I also see so much potential and need for it.

Cheers

[Mike]

@Cabelo - if you are trying to only look at records where the “Current Option” is true and you will usually just work with those records, as @Steve suggested, look at using Slices first. This can make things a lot easier down the line. I often have a slice behind each table to allow me to easily remove “inactive” records.

Check here:

Slices: The Essentials

Once you look at that, you will know better if you want to also use ValidIf or Select statements for your use case (as @LeventK suggested) . But, i would read about Slices first…

[Cabelo]

Wow… @Mike @Steve and @LeventK
It is scary how many possibilities the slices feature have just opened to me. One feels like dropping out of everything else just to have the time do explore and learn some more.

Having said this, unless I am missing something I still haven’t achieved my goal yet. Okay I now have a slice with all Active/Current hotels. But I do not see an option to use the slice as if it was table (as a ref). So although I have a slice with all active hotels I still haven’t figured out how to list each individual active hotel referenced to that day on the main table.

I’m sure there must be a way to do this but I haven’t been smart enough to find it yet

I’m in love with all of the possibilities…

Thank you guys very much for still having the patience to help me out…

[Cabelo]

Guys,

I’ve played a lot with the slices over the last few days.
I have a bunch of slices now and they are awesome.
But I still haven’t been able to achieve what I need with them yet so let me explain again what I need to do.
So I have 2 tables:

Table 1- This is the main table with a lot of the information for each day of a tour. The column “town” is marked as the key.

Table 2 - All hotels I have been visiting: for each hotel there is a row with multiple columns, ex number of rooms, prices, if there is a pool, if there is a bar, etc. The last column is a Yes/No question for “active”, meaning is this the hotel we are currently using in this town.

There are also tables on this app but they don’t really matter here. When viewing the main table (Table 1) I need to also see the hotel I’m currently working with in that town on that day of the tour. Table 2 might have a bunch of different options but I don’t want them to show up on this view of each day (or each row) of Table 1, I just want to see the “active” hotel, the one with “yes” (True) on the last column.

It doesn’t seem that SHOWIF nor Slices are working for me. Maybe Deeplinks? Any other suggestion?

[LeventK (Levent Kulacoglu)]

@Cabelo
You can add a Virtual Column to Table1 with below expression where the content of this Virtual Column will appear as an inline view in each town’s Detail View. Considering you have also a [Town] column in Table2:

SELECT(Table2[Key],AND([Active?]=TRUE,[Town]=[_THISROW].[Town]))

Just I’m not sure about what you had mentioned here:

When viewing the main table (Table 1) I need to also see the hotel I’m currently working with in that town on that day of the tour.

Is there something like a [Date] column also to be considered in Table2 for filtering the records besides the town and the active status? Can you elaborate this part a bit more?

Another approach could be this:
Provided you have created a slice of Table2 with respect to [Active?]=TRUE, and you have a ref between Table1 and Table2, you can slightly alter the REF_ROWS(…) expression in the [Related Hotels] virtual column to reflect your slice:

REF_ROWS("YourSliceName", "KeyColumn")

With this, the inline view content for Table2 will be displayed as per your slice.

[Cabelo]

@LeventK
Thanks you very much, your approach #2 worked really well…
This is great!
Now if you look at the screenshot you will see that the reference for the “meal” table has a bunch of icons out of which I can get email addresses, phone numbers etc that can make things a lot faster.
The reference for “hotels” slice doesn’t do that.
Is there a fix for this?

[Aleksi (Aleksi Alkio)]

Your ref view’s option “View type” is set as “Gallery”. Set it’s type as Deck view.

[Lynn]

Hi @Cabelo When making a slice you can set the actions to be associated with it and also in your deck view you can select which actions to show.

[Cabelo]

Yes Yes Yes!!!

@LeventK @Steve @Mike @Aleksi @Lynn

My app is “light years” ahead of what it was a couple of weeks ago thanks to you! It works! The problem is that for every new feature I learn I get new ideas
I am using the slices everywhere now! I started an app for helping with my bookings and I will be posting new questions very soon!

THANK YOU!

[Cabelo]

The app is going great but today I added another reference which is behaving a bit differently than the other 2.
As you can see on the attachment below, although I have 3 references (hotels, meals and campgrounds), if I don’t have related establishments in these towns nothing shows up, but for campgrounds I still have the “related campgrounds” with zero views and I would prefer to have noting there because the plan is to have quite a few possible references and it will become too polluted if all empty ones show up. I’ve tried to find out what is different but couldn’t figure out.
Any insights?

Thanks a lot!!!

[Aleksi (Aleksi Alkio)]

You could hide that virtual inline list with the Show? formula like ISNOTBLANK([Related Campgrounds])

[Cabelo]

Thanks @Aleksi

What puzzles me is that I don’t find what is the different on this reference compared to the other ones for it to behave differently.

Anyway I have tried to do what you suggested but I’m not sure I did correctly.
I tried using that formula in the virtual campground list of the main table where the key column is, is that correct?
Whatever I try it always gives me an error…

Thank you!

[Aleksi (Aleksi Alkio)]

The formula should go to virtual list column where you have that REF_ROWS expression… and it should go to option Show?. If you don’t have any child records, it won’t show that inline view. You also need to set the UX > Options > Apply show-if constraints universally as ON.

[Cabelo]

@Aleksi
Thanks for your patience.

I tried typing only IFNOTBLANK and the test runs fine but as soon as I save it I get the following error message:

Then I tried it followed by the name of the column as per your example and then it already gives me an error:

I’m loving this but I wonder how hard it is to learn it without getting yourself into smoking or drinking badly

[Aleksi (Aleksi Alkio)]

Your brackets are in a wrong order… Try ISNOTBLANK([…])

[Cabelo]

@Aleksi

Thanks that was extremely embarrassing.

It works like a charm!

My apologies and thank you again!