Good morning everyone!
I'm trying to create a sav...
# generalk
Koon Kabob
10/09/2024, 3:19 PMGood morning everyone!
I'm trying to create a saved search to show the Sales Order amount and Invoice amount for each Location we have for these time periods: Yesterday, MTD, YTD. So for example there would be a column Sales Order Yesterday and Invoice Yesterday and so on for the rest of the periods.
I have it almost complete with a bunch of formulas. However, I just thought if there was a way to get the internal ID of Sales from the Income Statement? This would help tremendously
m
MGBC
10/09/2024, 3:21 PMLike the internal ID of the income account?
k
Koon Kabob
10/09/2024, 3:24 PMYes. More specifically the amount. I know I'm asking for too much, but if there is an Internal ID for the amounts that would be perfect
m
MGBC
10/09/2024, 3:26 PMI’m not sure what you mean exactly but this is the formula I would use for this
CASE WHEN {type} = ‘Sales Order’ AND {accounttype} = ‘Income’ AND ({today} - 1) = {trandate} THEN {amount} END
MGBC
10/09/2024, 3:27 PMThe his will validate if the transaction is a sales order and the date is yesterday
Then only get the amount from the Income GL line
MGBC
10/09/2024, 3:27 PMThen just copy this and change sales order to ‘Invoice’
MGBC
10/09/2024, 3:28 PMThen you would make it a summary search using Group for location and SUM for these formulas
MGBC
10/09/2024, 3:30 PMTo get month to date change the date formula to this instead
{trandate} >= LAST_DAY(ADD_MONTHS({today},-1)) + 1 AND {trandate} <= {today}
k
Koon Kabob
10/09/2024, 3:31 PMThank you so much. I'll give this a shot.
To clarify to be able to get these amounts on the Income Statement which I believe your formula does 🙂
m
MGBC
10/09/2024, 3:32 PMYes you can’t get those numbers directly. There is no internal reference to that
You would have to replicate how that number is calculated (which my formula will do)
k
Koon Kabob
10/09/2024, 3:33 PMPerfect! Thank you so much! I really appreciate it 🙂
2 Views