0

Get data from unlinked table

Never finish!

I have "Service" table that contains:
Insurance End Service Date
Linked Table "Car Registry" Data

From a "Repair" table I have to make a formula to calculate the price the customer has to pay.

I cannot extract information from a disconnected "Service" table to understand if I need to make a discount on auto replacements if the customer has purchased the insurance service for the current year.

If I want information about the current record trying to retrieve the linked table ID, the ninox validator does not accept the selected linked table column, telling me that the linked field does not exist and that no value is returned.

First I tried to rename the machine data field in the various tables because I didn't know how to pass the filter to read the data since they were called the same way.

But I still can't do the formula to get the value.

This is the query I do:

let isInsuranceDiscount := cnt((select Services)[text(Service) = "warranty extension" and text(format('expiration date', "YYYY")) = text(format(today(), "YYYY"))and 'Services machines registry'.ID = this.'Intervention machines registry'.ID]);

let amount:= 0;
if isInsuranceDiscount > 0 then
    amount:= 10 + Km * 0.35 + 'Tempo impiegato (min)' * 0.8 + (sum('Ricambi Interventi'.Totale) - sum('Ricambi Interventi'.Totale) / 100 * 30);
    text(amount);
else
    amount:= 30 + Km * 0.4 + 'Tempo impiegato (min)' * 0.8 + sum('Ricambi Interventi'.Totale);
    text(amount);
end

 

"Intervention machines registry" and  "Services machines registry" is same table "machines registry".

I hope I have explained myself well...

Thanks for your kind help

Mauro

1 reply

null
    • Fred
    • 1 yr ago
    • Reported - view

    In Ninox, if you want to use the default record number then the easiest way is to just use the name of the reference field. You don't need to use Id. If you do want to use the Id field name then it has to be "Id" not "ID".

    I've also encountered issues where "this" doesn't work in the middle of selects, so you have to put "this" in a variable.

    let t := this;
let isInsuranceDiscount := cnt((select Services)[text(Service) = "warranty extension" and text(format('expiration date', "YYYY")) = text(format(today(), "YYYY"))and 'Services machines registry' = t.'Intervention machines registry'])

    If you only want the year from a date field then you can use year() instead of format.

    let t := this;
let isInsuranceDiscount := cnt((select Services)[text(Service) = "warranty extension" and year('expiration date') = year(today()) and 'Services machines registry' = t.'Intervention machines registry'])

    I hope this helps.

Login to reply

Content aside

  • 1 yr agoLast active
  • 1Replies
  • 50Views
  • 2 Following