the expression box is headed:
"Each portal record will be visible when:"
So your filter expression should just be Orders::orderdate = Globals::gdate
but where does gdate get its value ?
Thanks, I actually tried that but it still doesn't quite work. To simplify I tried to just 'hard code the date I need.
I have 5 dates in the portal
If I enter
Orders::orderdate = "14/09/2011" ---it doesn't work. I get no rows in the portal
If I enter
Orders::orderdate < "16/05/2012" -- it works. I get a single row with the 14/09/2011 record
It seems the > and < operators work but the = does not
In reply to your question gDate comes from user input. Likewise if I use
Orders::orderdate = Globals::gdate ----it doesn't work
But if I use > or < then I get results
I recall that once I had to 'deconstruct' the Date into month, day, year, then do the comparison with the Date(m,d,y) function
Orders::orderdate = Date (month(gdate), day(gdate), year(gdate) ) always assuming that gdate is defined as type date :)
or set up a new TO with a relationship linking gdate = orderdate, then use this in the portal ?
I just realised it was a case of the date format. Thanks for your help.
I would rather have my portal display only rows that are a result of a SQL query eg.
Select orderid, orderdate, productcode, qty
where order date = gDate and ...; etc
Basically for a given customer - the table that the layout is based on - I want the portal to only show SOME records from the related orders table.
I know SQL better than FMP and could do it in a jiffy with SQL but I don;t know how to implement that in FMP
sorry Dino - I'm not up to SQL - yet!
Thanks for your help. SQL is great but I don;t know how to use it in FM
Can I indulge one more time??
I can make the date filtering work now that I know the date formats have to match. But here's the problem.
Remember the date will come from another field
Orders::orderdate = Globals::gdate
But.. that only works if the there is a date in Globals::gdate. I want the portal to filter by that date if there is a date inputed into gDate but if there isn't a date then I want the portal to show all records (all dates).
I can't achieve that. If gDate is empty the filter will show no records. I want it to show all records
OK, then your filter expression could be:
If (Isempty(Globals::gdate) ; Orders::orderdate =/ Globals::gdate ; Orders::orderdate = Globals::gdate ) (=/ means not equal to)
I don't have a sample set up to try this, but I suggest that this could be the go
It doesn't like the =/
Do I need to escape it or quote it? Says its expecting a 'number, text constant etc here'
you need to use the 'not equal' symbol in the expression builder, see screen shot....
(can't type that in here )
I tried it but still no luck. I get no records regardless of whether there is something in gDate or not
Seems to me that you need this expression:
Is Empty ( Globals::gDate ) OR Globals::gDate = Orders::orderdate