Reference if?

Cabelo
Participant V

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โ€ฆ

0 32 3,348
32 REPLIES 32

LeventK
Participant V

@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.

@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.

2X_3_3161e2a9287a0f0fa109c503ec9a3058e9895073.png

Thanks again!

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

@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:

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โ€ฆ

Steve
Participant V

If you use a slice as your โ€œmain tableโ€, you can use the sliceโ€™s row filter to omit the desired rows.

Cabelo
Participant V

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
Participant V

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?

@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:

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.

@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?

Your ref viewโ€™s option โ€œView typeโ€ is set as โ€œGalleryโ€. Set itโ€™s type as Deck view.

Lynn
Participant V

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
Participant V

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
Participant V

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!!!

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

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!

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.

@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

Your brackets are in a wrong orderโ€ฆ Try ISNOTBLANK([โ€ฆ])

@Aleksi

Thanks that was extremely embarrassing.

It works like a charm!

My apologies and thank you again!

@Cabelo - no worries - you should see some of the mistakes I make โ€ฆ you would just laugh. Thatโ€™s why this community is great! Everyoneโ€™s willing to help others along, and nobody makes fun when we trip over the silly stuff

Youโ€™re welcome

Cabelo
Participant V

Well, now that I understand a little bit more what appsheet is and what it can do (but not really how), Iโ€™ve decided I need to start from 0 again.
Originally what I had done was to get a workbook I work with and turned the whole thing in an app without much planning. It was good to understand things a bit, but as I started getting creative it got out of control since trial and error works better on a smaller scale than with a massive project. I wonder how many people do what I did and end up giving up.

So although I know that this not gonna be the final version, I want to start all over again but table by table.

As a context, this is for a commercial bike tour we run and lasts almost 6 months

I want to start with:

Table 1 - A table with lots of possible combinations of daily rides (From / To / Distance / Etc) - I think that this is what people call a child table

Table 2 - A table which would be the main table which Table 1 would reference to. On this table I would have 3 columns:
*Stage # (Day 001, Day 002, Day 003, etc)
*From (dropdown menu of towns)
*To (dropdown menu of towns)

My idea is that once I select From and To (on Table 2) on the dropdown menus, appsheet would find on Table 1 a column that match those 2 criterias and create a โ€œRelated Routeโ€ field on Table 2 with the correspondent information from Table 1.

Is this a stupid new beginning?

Thanks!

Dear all;

Iโ€™m having a similar issue: I have two tables, one for creating new orders [Pedido], and another for dispatch of the products [Expediรงรฃo].

I want to only those orders that are not yet dispatched to be seen when the expedition sector pulls the list of orders. I have created a ref from the dispatch [Expediรงรฃo] table to the orders [Pedido] table

I have done what Iโ€™ve read in this post, and this is what showed up to me:

What can I do? Since now, Iโ€™m very thankful for your help.

regards;

Fรกbio

Change the column type from Ref to List.

Thanks for your prompt reply, Steve.

Iโ€™m using the Ref type due to the need of importing info from the order. Is there a way to do it with the List type of column?

Yes, make it a List of Ref.

Steve, it worked. The layout is working just fine, exactly like I have planned, even better than before.

3X_d_6_d68eb7f38dc005aa7f65cb3180132f843e06723a.png

But when I click on it, it shows a resume of the order , such as below:

3X_e_a_ea8cc8b5b57511c1f208987a9ca793cd82b28aae.png

Would there be a way to, at the click, just import the data from the order to the dispatch?

I donโ€™t want the dispatch team to have access to all of the data from the commercial team, nor have access to the new orders form, such as below:

3X_f_2_f294954aef146094df8b030a9f51dd736eb90d51.png

Would there be a way to create a filter across tables using Ref column?
Iโ€™ve been searching for it since yesterday, when I read your reply, but I couldnโ€™t find it.

Besides that, would there be a way to choose instead of the date it was created ( Which is one column), another column ( scheduled date to ship )? Even using CONJECTURE?

Canโ€™t thank you enough for your patience with this new learner.

Regards;

Fรกbio

When you click on what?

When I click on the order ( which is being referenced by the name of the costumer [As an example in the print screen, Fabio])

Well, Iโ€™m not sure how to fix your problem. I donโ€™t do much with adding new rows within a form view, so I have no experience to share. You might try asking support@appsheet.com?

Thank you very, very much Steve!

Deeply appreciate your attention.

Regards!

I donโ€™t think my text was too clear, so Iโ€™ll try to explain it in a different way:
Dear all;

First of all, congratulations on your amazing platform. Iโ€™ve been learning/ developing for a couple of months, and everything is extremely intuitive.

Iโ€™m creating a management app, and Iโ€™m having a minor trouble with a column that needs to be filtered:

image.png

The above table, Expediรงรฃo ( Dispatch ) is referencing to the Table below ( Pedidos), in which the selling orders are emitted.

image.png

I need to filter only those Selling Orderss ( Column named Pedido ) which are not yet shipped. Is there a way to create a filter across tables to do it so?

Regards;

Fรกbio

Top Labels in this Space