Help with relationship

[Lola]

Hello All,

 

I have a problem with my relationship. In my Dashboard I have an alert that will tell me how many materials are expiring & to be retested but my problem is it showing me a  "?"

 

The relationship graph is below

 

 

Dashboard Fields

Constant = 1

Constant Today = Get (CurrentDate)

Global Number = Number field Value is 1

 

Material_Expiring Today

Constant = 1

Material Expiry Date = Date Field

Material Stock = Calculation (Material Quantity Received - Sum ( Material Transactions::Quantity Dispensed )

 

If I change my Material Stock into a Number Field It will show my desired output.

 

Please see attached file that Im working.

 

Any help is highly appreciated.

 

Thank you

 

Materials (2).fmp12

Edited September 5, 2017 by Lola

[bruceR]

Quote

If I change my Material Stock into a Number Field It will show my desired output.

That is the correct result. Why is this field involved in the relationship?

And - for that matter; why is CONSTANT involved in the relationship?

Edited September 5, 2017 by BruceR

[Lola]

I used the constant in the relationship to match the same record. 

Is there a way that I can make my Material Stock a Number field but still always show my current stock when I change my Quantity Dispense Field? Thank you.

[bruceR]

6 hours ago, Lola said:

I used the constant in the relationship to match the same record. 

This makes no sense.

[Lola]

I removed the constant in the relationship..still did not change my result still showing "?"

[bruceR]

But you already know this answer. You cannot use the quantity part either, due to the unstored calc.