I have 3 tables
- TRACKING NUMBERS (a basic list of key ID's that I use to link all parts of a project)
- PURCHASE LOG (a place for a company purchaser or shipper receiver to log purchases - Using the Tracking numbers as a P.O. number)
- TIME LOG (a place for field technicians to log time to a tracking number, and also select which purchases they have used on a job)
A field technician makes a time log entry to a specific tracking number. The time log pulls up all purchases for the tracking number in an inline view.
I have 2 actions set up.
1. The red "X" - Both shows unclaimed purchases for the job, & if you tap it when its grey it clears a claim on a purchase.
2. The green "โ" - Both shows a claimed purchase for the job, & if you tap it when its grey it claims a purchase.
Currently when you tap on the "โ" it pulls up the INPUT() window and makes you pick from all of your time inputs related to the tracking number. I was wondering if it was possible in the action via a formula to have the action search the REF table and if it only finds 1 possible value to return that value, else ask for user INPUT().
I have attempted to add an IF() to the claim purchase action with no success as follows:
IF(
COUNT(
SELECT(TIME LOG[KEY ID],
AND(
[TRACKING NUMBER KEY ID] = [_THISROW].[TRACKING NUMBER KEY ID],
[REPORTED BY] = USEREMAIL(),
[DATE] = TODAY()
),FALSE)
)>1, INPUT("PICK TIME LOG:",[TIME LOG KEY ID]),
ANY(
SELECT(TIME LOG[KEY ID],
AND(
[TRACKING NUMBER KEY ID] = [_THISROW].[TRACKING NUMBER KEY ID],
[REPORTED BY] = USEREMAIL(),
[DATE] = TODAY()
),FALSE
)
)
)
Is it just not possible or is my formula logic out to lunch?
Solved! Go to Solution.
Not possible. INPUT() can only be used as the sole function in an expression, so you can't have some sort of condition to determine whether the field is updated through an input or something else.
Another option is to use more than one action, both having the green tick icon but one going to the input and the other just setting the value directly. You could then use an expression to determine which to show.
User | Count |
---|---|
33 | |
11 | |
3 | |
2 | |
2 |