Search for value based on 2 conditions (one of them a range of dates)

[Rosa_Bali]

Hi! I have 2x tables, let's call them:

PRICES. With the prices per room per night per hotel & their room categories, and based on date. For example, at Hotel "A" a Deluxe Room from 01 to 02 Jan costs $100. Or a Deluxe Room at Hotel "A" from 02 to 04 Jan costs $275 ($125 + $150).

QUOTATION. With an itinerary of "Day 1" - Stay at "xx Hotel/Room"; "Day 2" - Stay at "xx Hotel/Room". For ex., this quotation below would be for someone staying from 02 to 04 Jan at Hotel "A", at a Deluxe Room, 2 nights, $275 (as per previous example).

My app is based on "Quotation" table. I am trying to search for a formula to calculate something like:

Search on table "Quotation" for the date of [Day 1] among all the dates in table "Prices" from [01 Jan 2023] to [7 Jan 2023], and paralelly search on table "Prices" for the value of Table "Quotation" column [Hotel Option A] which matches on table "Prices" in column [Company,Service].

The same for Day 2.

The result on Table "Quotation", column [Total] should be, let's say, as the example, $275.

Someone can help please? @Steve and other kind colleagues, please.. 🙂 I've already tried all... 

This is my 3rd complex app already. Hopefully soon I am already ready to help others too 🙂 Thanks!!

[rakhie_urs]

Are you asking for an expression/ formula on sheet or the appsheet?

[Rosa_Bali]

On appsheet, sorry 🙂

[TeeSee1]

@Rosa_Bali 

As @rakhie_urs probably has a similar question in mind, the way you have your tables (whatever screenshots you have) is not going to work very well in AppSheet.

I have quickly created a snippet.

https://www.appsheet.com/Template/mobilepreview?appId=9cb714b3-3316-44a5-8cd4-0bd49ce39c4a

Now you definitely need to tweak this before you move it to production but the main idea is that you have a quotation and a quotation detail tables (parent - child).

As for the prices table, what I have is a compound key (hotel & date). Of course you can do it differently like (generic id, hotel, grade, date, price).

The app is just for illustrative purposes.

See "HOTEL Quotes" for the inside

https://www.appsheet.com/portfolio/3401559

Hope this helps you a bit

[Rosa_Bali]

Hello TeeSee, 

THANK YOU very much (in capitals) for the effort to make an illustrative app. I would love to use it, but in my app hotels mostly depend on the kind of tour input on that day. For example:

Day 1

Date: 24/02/2022

Tour: City Tour (visit Forest, Local market, museum)

Hotel: (here the system reads on the Google Sheets the value of the column at the right of "Tour", and reads "Splendid Hotel, Deluxe Room"

Therefore, it cannot read Splendid Hotel, Deluxe Room... and the date... although maybe I could say to Appsheets "find the value of the hotel suggested, and then concatenate it to the date mentioned in "Date". 

And difficult because then I should change all the structure of my app.. and I have it already very advanced. 

Is there maybe any other way, with which we still can match a date with a range of dates and getting the result..? (so that I still can keep all the format 😅)

Again thanks so much!

[TeeSee1]

This is  a difficulty we often have in this community Q&A. We do not have the details of the app that the person asking the question has created so far.

Maybe if you share exactly what you have, then we - community contributors - maybe able to help better.

[Rosa_Bali]

Dear TeeSee,

Thanks so much for your answer, and so sorry for the delay in mine.

The app is quite complex (it has many details inside) and is being internally developed for our company, so unfortunately belongs to our privacy, I really feel very sorry not to be able at this time to share the app here in public.

But what I can do is sharing it with you, if you like, adding you as an Editor. I know that maybe this is not the best way as we should share all publicly, but maybe as you have more experience, after you see it, you can tell me what and how to publish to help others..? Thanks so much once again

[TeeSee1]

you do not need to share everything.

the definitions for

PRICES, QUOTATIONS and perhaps TOURS?

[Rosa_Bali]

TeeSee,

I think I am getting it. As I am working only part time please allow me some days. I will come back with the solution if it works 🙂 (I have to remake the whole data base of hotels, and then, do the final try). Thanks so much!