From a reference database table how to get the service amount where 2 factors are involved

[Aditya_Vinayak_]

I have 2 tables:

1) Service Details: Which is basically like a database of all the services provided by me. The fields in it are Service ID, Service Name, Pet Size, Service Charge. The Service charge changes basis the service name & pet size.

2) Service Availed: This table takes a reference from the service details table based on which we choose the service name. I have put a pet size field also which calculate automatically based on ref. But for some reason I am not able to get the service charge.

In the service charge coulumn i used the below formula:

ANY(SELECT(Service Details[Service Charge],AND([Service Name]=[_THISROW].[Service Name],[Pet Size]=[_THISROW].[Pet Size])))

But the field only disappears.

please assist

[Marc_Dillon]

If service_availed[service name] is a Ref column, then the value in it would be the [service ID], which would never match to any Service Name.

Sounds like you're using a Ref column, but the record selected is not always the actual record to use, because there are more than one records with the same service name (vary by pet size). For data clarity, I would have 3 columns in the service_availed table: [service name] as a non-ref dropdown, [pet size] also as a non-ref dropdown column, then a hidden auto-calculated Ref column that takes those 2 values to select the appropriate referenced record. From there, you can use dereference to pull any values, such as the charge.

https://help.appsheet.com/en/articles/1090811-dereference-expressions

[Aditya_Vinayak_]

Possible to elaborate more on this, I didnt understand...

[Marc_Dillon]

What do you not understand?

[Aditya_Vinayak_]

@Suvrutt_Gurjar @AleksiAlkio Please assist

[AleksiAlkio]

Another way is.. you have one row for each pet size and then you could read the price with the deref like [ServiceID].[ServiceCharge] as the service is already selected properly from the Ref column.

[Aditya_Vinayak_]

So I will try to explain what I am expecting maybe then we can see if we are on the same page or not.

Below are the tables in my app:

1) Customer Database - Which records all the customer information. 
2) Pet Details - Which records all the pet information & is a child to the Customer Database table.
3) Pet Breeds - It records all the pet breeds, pet type & pet size information. And is presented as a list in the pet details form through ref, and other respective details are autopopulated.
4) Service Details: Which records all the services that the facility offers, along with the charges based on the breed size. Something like: Overnight Boarding - Small - Rs 650
5) Service Availed: It records the services to be availed by the pet which it is referenced to. It is a child to the customer as well as pet details table. The form for this table has a field by the name of Service Name, which data type I have choosen as ENUM list & the base type as REF to the service service details table. In the suggested value I have put a formula: Service details[Service ID]. When i do this, in the service form, Service name field, becomes multiselect list.

I have a "pet size" field also, whose value autopopulates by dereference to the pet details table.

My issue comes when i have to figure out the service charge.

1) 1st issue is that how do i make the app to determine that it has to check the service name & the size of the pet & from service details table grab the charge?

2) Other issue is there is a possibility that one pet can take multiple services. Then how will the amount calculate?

Please advise if the structure of my app is wrong or there should be some changes. 

@Marc_Dillon @AleksiAlkio @Steve @Suvrutt_Gurjar 

[AleksiAlkio]

#1 - If I understood your table structure properly, you can sum the charge in "Service availed" table with a normal column as..
SUM(
SELECT(ServiceDetails[Charge],IN([ID],[_THISROW].[ServiceName]))
)
#2 - Yes you can have multiple "Service availed" records they are Refs already. Then you can sum the total in "Pets Details" table like SUM([RelatedServiceAvailed][Charge])

[Aditya_Vinayak_]

For the 1st one:

Above is the table structure for Service Details Table

Above is the table structure for service availed. For now Service name is a ref column to the service details table. 

Would your formula still work with this structure?

[AleksiAlkio]

No. You said earlier "The form for this table has a field by the name of Service Name, which data type I have choosen as ENUM list & the base type as REF to the service service details table." but now you are saying it's a Ref. So.. which one is true?

[Aditya_Vinayak_]

Apologies, Its a ref.

So before I was trying to choose multiple services. But now decided that will go with one service only.

Have i confused it?

[AleksiAlkio · Accepted solution]

As you are using Ref in "Service Availed" table for the details, you need to read the charge with the Deref like [ServiceName].[Charge]. Then in the "Pets Details" table, you can sum them all with the formula in virtual column like SUM([RelatedServiceAvailed][Charge])

[Aditya_Vinayak_]

Okay let me check on this then.

[Aditya_Vinayak_]

My dropdown list for the service name is like this...even though the service charge is different..