1 Reply Latest reply on Apr 1, 2014 5:59 AM by philmodjunk

    problem with a relation on a calculus field



      problem with a relation on a calculus field



           I have a database with three tables: months, incidencies and days, with the following fields:

           months: __kp_idmotn, year, month, num_incidences=count(incidencies::__kp_idincidences), zg_zero=0

           incidences: __kp_idincidence, date, year=year(date), month=months(date), total=sum(days::cost)

           days: __kp_idday, _kf_idincidence, cost

           incidencies and days a related by the fields incidences::__kp_idincidence = days::_kf_idincidence

           What I want to do is to count for each month the incidences of the month that have total>0.

           So I have set a complex relation between months and incidences:

           months::year = incidences::yerar

           months::month = incidencies::month

           months::zg_zero < incidences::total

           For this complex relation to work, I have had to set the calculus fields on incidences as indexed as needed, and it works fine for year and month. But I cannot set incidences::total this way, as it is dependent of another relation, and the field months::num_incidences show a question mark.

           What am I doing wrong?









        • 1. Re: problem with a relation on a calculus field

               Any field that is not an indexed field cannot be used as a match field in a relationship if placed on the "many" side of a one to many relationship. You'll need to use another method--either a different method for calculating the monthly total (ExecuteSQL() might work) or to calculate the value of Incidences::Total (You might use a number field that is updated by script trigger performed scripts each time a value in days is edited or deleted.)