Portal wont show related records when field is set as a calculation

[docasar]

Hello,

I am newbie to filemaker pro but love it   Said so, I am struggling a solution that I am building for management of holiday apartments, based on FMSP. I have created a table for bookings, and apartments, and now I am working with another table call Daytariffs that would give the price for each day of the year for each apartment. So far I have being able to show the available apartments for a range of dates, but what I am struggling now is to show the prices for those days. Basically, what I have done on the DayTariffs table is to load 365 days (records) then I have created a field called "Range" and put a calculations that shows "YES" if a date is within the dates I have set on the Booking layout as StartDate and EndDate (this dates are global fields). I have created relationship between Daytariffs and Bookings through ID_DayTariffs (Daytariffs table) = id_daytariffs (Bookings table). Later,  I have also created a global field called "BookingDAYS" on Bookings tables and set the field value as "YES", and I have created a new relationship that relates BookingDAYS = Range.

Then I have created a portal based on the the relationship between BookingDAYS=Range, on the Bookings Layout but wont show any records  I am stuck here because if I change the Range field to be just text without any calculation and I write "YES" then the portal works, but as soon as I put the calculation back, the related records disappear...

I have checked that calculated result on Range field is set as Text as well as BookinDAYS is a text field... also what have noticed is that the relationship graph for BookingDAYS=Range has this simbol at both sides "|---" when normally would have a three lines (like a folk) when I choose any other filed for that realtionship instead of Range.

I am attaching screenshots of relationship graph, booking layout and DayTariffs Table

Any ideas what I am missing here?

Thanks a lot.

Luis

[comment]

I could not fully follow your description. It looks like you have a calculation field that references a global field. Such field cannot be stored and indexed, and if you use it as a matchfield in a relationship, the relationship will work only in one direction. The screenshot you posted shows a relationship where both matchfields are unindexable: such relationship cannot work at all.

I am not sure what you want your portal show. If you enter a range into two global fields (in any table), you can show all DayTariffs that apply to that range by defining the relationship as:

AnyTable::gStartDate ≤ DayTariffs::Date
AND
AnyTable::gEndDate ≥ DayTariffs::Date

I am not sure how helpful that would be, though, because these tariffs do not necessarily apply to the apartments that are available in the given range. It might be more useful to have each available apartment calculate its price for the given range. In such case, you would want to have the two global date fields in the Apartments table, so they can be used in a relationship to the DayTariffs table.

 

56 minutes ago, docasar said:

I am working with another table call Daytariffs that would give the price for each day of the year for each apartment.

Do you really have a different tariff for each day? I suspect it would be quite sufficient to have a record for each season for each apartment.

See also:
http://fmforums.com/topic/33507-date-range-and-data-look-up/#comment-155473
 

[docasar]

Hi,

I am sorry I couldnt state my problem correctly. 

3 hours ago, comment said:

Do you really have a different tariff for each day? I suspect it would be quite sufficient to have a record for each season for each apartment.

I have the whole price calcualtion formula "programmed" in excel, and what I am trying to do is to adapt it to Filemaker. Regarding your comment, the reason each day has a different price is because sometimes booking dates can fall within two different seasons so I thought that in order to account for that the best way I could think of it was to show in the portal the dates between the StartDate and the EndDate and sum their price dates. These prices come from 5 different seasons that I have set with their StartDate and EndDate, however I am just uploading the data from excel directly, however, I believe that with the info on your link I will be able to change the way I can produce the price for each day with the link you provided 

My challenge now will be to make the price calculation for the period to show in each apartment when listed on the portal of free apartments. But I will explore the info and the link to find a solution.

I cant state enough the gratitude I feel when getting support from the forum! Thanks a lot!!!

Luis.

 

3 hours ago, comment said:

I could not fully follow your description. It looks like you have a calculation field that references a global field. Such field cannot be stored and indexed, and if you use it as a matchfield in a relationship, the relationship will work only in one direction. The screenshot you posted shows a relationship where both matchfields are unindexable: such relationship cannot work at all.

Moreover, I asume that I could not go around just by setting a new field with a calculation that shows values from Range, right?

Thanks!

[comment]

5 hours ago, docasar said:

I asume that I could not go around just by setting a new field with a calculation that shows values from Range, right?

Only if you were the only one using the solution. Otherwise a user looking for one range would ruin it for other users looking at other ranges.

Edited September 12, 2016 by comment