0

Trying to filter an if statement, I've tried pretty much everything I can think of...

I have a table called 'Customers' it's a parent of 'Orders' which is a parent of 'ads'

Ninox seems to have automatically created a second instance of Ads, and named it Ads2. Multiple relationships I assume?

 

In the table Ads, I have a "Stage" field that is a choice field for "Live" "Lost" "Completed" "Proposed" .

For our sales efforts, I'm trying to set up an alert for when one of my customers is down to two "Live" advertisements left. So I did this:

 

if Orders.Ads2.Stage < 3 then
styled("Order Shortage!", "red")
end

 

This works, right now, because they're ALL "Live" -- it dawned on me that eventually customers will have ads that are not "Live" -- after they pass the current issue, the individual ads will be switched to "Complete" -- but my if statement won't filter those, so I need to specify that I only want "Live" ads.

 

"Stage" is a choice field. I've tried every version of this I can think of to filter the ad stage I'm looking for "Live" -- with no results. "1" is the number of the choice "Live" -- I've tried both. 

 

if Orders.Ads2.Stage ="1" < 3 then
styled("Order Shortage!", "red")
end

 

This applies "Order Shortage!" in red, to every customer. I only want the customers who have  less than two "Live" ads remaining.

 

How can I do this??

 

Help!

5 replies

null
    • Ninox partner
    • RoSoft_Steven.1
    • 3 yrs ago
    • Reported - view

    In a formula field in the customers table:
    let me := this;

    let c := cnt(select Ads2 where Id= this and Stage = 1);

    if c < 3 then styled("Order shortage!","red")

    end

    : this is not tested, it might need some tweaking.

    Steven

    • Willamette Living Magazine
    • Scott_P_Alexander
    • 3 yrs ago
    • Reported - view

    Hmm no go, yet. What is "me" ?

    • Willamette Living Magazine
    • Scott_P_Alexander
    • 3 yrs ago
    • Reported - view

    This line is causing an error:

    let c := cnt(select Ads2 where Id= this and Stage = 1);

    Says Ads2 "Table not found"

    If I change it to just Ads, it removes the error, but doesn't work.

     

    Tried to click to select the table using the box on the left, gives me "Orders.Ads2.Stage"

    Then gives me "Symol expected:,at line 2, column 43 

    Here's all of line 2: let c := cnt(select Orders.Ads2.Stage where Id = this and Stage = 1);

    Coulmn 43 is the space right after "where" -- not sure what symbol it's expecting there??

    • Willamette Living Magazine
    • Scott_P_Alexander
    • 3 yrs ago
    • Reported - view

    Wait...  trying to change Orders.Ads2.Stage changed to just Orders.Ads2 -- changed it by clicking on the box on the left to drill down for more of an "absolute" reference. Now it sees the table, but now error: Symbol expected:,at line2,column37

    Here's all of line 2 now:

    let c := cnt(select Orders.Ads2 where Id = this and Stage = 1);

    • Willamette Living Magazine
    • Scott_P_Alexander
    • 3 yrs ago
    • Reported - view

    OK, I think I've worked it out.

    I suspect there's something fishy about trying to do a count two tables away.

    So in Ads I added a formula called "live ad count": if stage = Live, then 1 (gives me a 1 or null)

    Then in Orders: sum("live ad count")

    Then in Customers : if Live ad count summary<3 then red and "low ads alert!"

     

    Seems to do the trick.

Login to reply

Content aside

  • 3 yrs agoLast active
  • 5Replies
  • 370Views