Date filtering

[SpaceCoyote]

I’m having an issue with adding date filtering to my ordering app. I have a table of promotions with start and end dates that is used a reference from my orders table. A straight up Ref will pull in all the promotions as a drop-down selector as desired, but if I try to add a filter with dates I get an error.

I tried using FILTER, I tried SELECT, but in both cases I get “Cannot compare List with Date in (PromoName[StartDate] > TODAY())”

Orders Table
CustName | PromoName (Ref) | Qty

Promos Table
PromoName | StartDate | EndDate

Any help is appreciated

[Bellave_Jayaram]

Try this:
[PromoName].[StartDate] > TODAY()

[SpaceCoyote]

Would you mind explaining where to use that? If I use it on my Orders table, PromoName reference as a ShowIf, it doesn’t show any info for new orders. It will show orders already placed, and the promo name will be blank if it is expired. Using it as ValidIf denies the entry, so that’s a start at least.

If I use it on the Promos table as a ShowIf, I get "Error in expression [PromoName].[StartDate] :

[WillowMobileSys]

It might be helpful to understand that when you specify a Ref column you are referencing (pointing to) a complete row from the other table - not just a single column. So in your Orders table, you could have named your Ref column anything…I might suggest to call it simply Promo. Just a tidbit of info.

That Ref column is only valid within the context of the table row you have it defined on. In your case, the Orders Table. So you can use the “dot” notation [PromoName].[StartDate], in the context of an Orders Table row nearly anywhere you can normally refer to a column.

You cannot use [PromoName].[StartDate] in a Promos table row context since the ref column is NOT defined there.

The Show_If result will depend on where you are placing your expression. There are Show_If’s on each column as well as for each View. There may be some others I am not thinking of right now.

Please let us know if you are having any additional trouble.

[SpaceCoyote]

That was helpful, thanks. I guess I should have gone into more detail. Orders are actually split into two tables - a header that holds the promo and rep info, and the lines that has the names and quantities of the people that want to order them. I actually used the structure of the "Order Capture’ sample app, but reversed the products and customers tables to suit my needs.

I’d like to rep to be able to see existing orders that they placed, but not allow extra orders to be entered after they have expired. If I put the ValidIf constraint on the order header, it will successful deny them adding them that way, but they can still go into existing lines and place them that way. I need to figure that part out.

Edit: The order lines table uses the initial value of SELECT(Orders[PromoName], [Order Id] = [_THISROW].[Order Id])

[WillowMobileSys]

It might be even more helpful to show some images of what your app and data tables looks like.

When you talk about placing an Order. I think of a user action. Typically what might happen is that you would show the list of Promos and then have an Action button the user taps to “Place the Order”. The Action button takes the user to the Order entry Form. When the Promo expires, the button is hidden so users cannot place an order. But a rep can still select the Promo row and drill into the details and see what orders were placed.

How do users currently in your app, request to place an order?

[SpaceCoyote]

Thanks, John. Your tip about actions was very helpful, especially when I read that system actions can have conditions on them as well. I ended up setting a behavior to the Add function, using a formula with my date criteria.