Need some help from the pros

[amerioca]

Hi,

 

I am working (better trying or playing around) with FM for some 5 years now. I am not a pro or anywhere near there but in course have come up with some db's serving my purpose well. However, comes the time comes the limits....

 

I am currently working on a simple invoicing module. (have set up a separate very small db for trial and testing as I do not want to spoil the Starting Point 4 solution template which I have amended to my requirements to an extensive stage already)

 

4 Fields, namely 

 

Current Date: Date Field (system time - have to refresh the window as no real time clock counting solution available for FM)

Due Date: Date Field - Date when the invoice is due

Status: Text Field - Active, Paid, Overdue and Aging

Invoice paid: Date Field Date when the invoice was paid

 

Purpose is to autoenter and update by calculation the Status. 

 

Case ( Date > Date Due; "OVERDUE" ;Invoice paid  ≤  Date Due; "PAID" ; Date > Date Due +14 ;"AGING";Date Due ≤ Date;"ACTIVE";"")

 

2 Problems. a.) There seems to be something wrong within the AGING calculation, which is supposed to kick in after 14 days overdue and b.) I would like to override and change the Status field content from for example "Overdue" to "Paid", the moment the field "Invoice Paid" is not empty. Otherwise it would have to be changed manually or would stay on "Overdue" even when an invoice was paid after due date. How am I going to do that??

 

This may seem easy for many (and I do envy you for that) but it certainly ain't for me. Any help highly appreciated.

[comment]

The Case() function evaluates the tests in the order they are written and returns the result of the first test that evaluates as true. If I understand your order of priorities correctly, you want something like:

Case (
Invoice paid ; "PAID" ;
Date > Date Due + 14 ; "AGING" ;
Date > Date Due ; "OVERDUE" ;
"ACTIVE"
)

--

P.S. I don't see why you need to dedicate a field to keeping the current date, when you can use the Get (CurrentDate) function directly in the calculation.

[amerioca]

Tnx a lot for your help. I copied and pasted your calc and it seems to work just fine. The reason, I was considering a real time clock, was simply because the  Get (CurrentDate) command only gets the date on refreshing the window and if used as a timestamp stays on the same time throughout the user session. But however, you are right saying that there is no real critical reason using a running clock as people turn their computers of once leaving the office.

 

Tnx once again. Appreciate your helpl

[comment]

the  Get (CurrentDate) command only gets the date on refreshing the window

 

I am not sure I understand your concern here. An unstored calculation using Get (CurrentDate) will re-evaluate on every screen refresh. Referencing another unstored calculation field = Get (CurrentDate) instead will not make any difference.

 

The only time this can cause a "problem" is when a user is staring at the screen, doing nothing, and the clock goes past midnight. Until some action is taken, the data displayed on the screen will remain unrefreshed, reflecting the states of yesterday. In situations where this is a real concern one can use an OnTimer script to refresh the screen periodically.

 

If you want a real-time, ticking clock (for display purposes only!), use a web-viewer.

[amerioca]

Tnx so very much. All works fine, the way you have scripted it. Now I am running into my next task. All my records have the relevant fields:

 

Current Date

Invoice Date

Due Date 

Invoice paid

Total Amount Due

 

I would like to create a chart with a monthly gross revenue reflection, meaning JAN, FEB, MAR etc. It is just meant to compare gross revenues (total amount due) from month to month, but my fields basicaly reflect days not months I don't know how to convert the dates into months within the charting tool.

 

Help is greatly appreciated.

[comment]

Before you can chart, you must be able to group. For this purpose you need a calculation field to return a common value for every record in the same month, for example:

Due Date - Day ( Due Date ) + 1

which returns the date of the first day of the month in which payment is due.

[amerioca]

Tnx for the prompt reply. I don't know if I understand correctly. I have created a new field with exactly that calculation  (named Test_Date) and tried to return results as text and date. Both return into an blank field. I am obviously doing somehting wrong. Could you explain step by step?

 

Tnx again 

[comment]

The result of the calculation should be Date. I don't know why you're not getting a result. Are you sure that Due Date is a Date field and Test_Date a Calculation field (not Date/Text field with auto-entered calculated value)?

[amerioca]

You were right (again .-). Now "test_date" results into the first day of the month of "invoice_date". So how am I generate a monthly/annually chart, integrating the field? I assume I have to go via a summary field somehow??

 

1000 thanks

[comment]

Here's a very simple demo.

 

 

---

P.S. Please update your profile. It shows v.9, but charts weren't introduced until v.11.

MonthlyChart.fp7.zip

[amerioca]

Hi there and tnx a lot. I have updated my profile, which I have not done a lot as I am not really working with FM every day. But should be up todate by now.

 

As for your solution, It works just about fine when I change the respective fields to the fieldnames of my own tables with one little bug which is probably based on data entry within the records (Nov 2014 shows twice with different data on the chart) I am currently looking into it.

 

I had sample records from 2013 and 2014. Is there a way to have it only display the monthly records for one year at the time? Not really that critical as I have set up the chart in a way that it fits a lot of data but it probably will look sort of weired after 4 or 5 years with all the data. 

 

Tnx again for all your help

[comment]

Is there a way to have it only display the monthly records for one year at the time?

It will display the records in the current found set - so you just need to perform a find for the date range you want to chart, instead of the Show All Records placeholder in my script.

 

[amerioca]

Tnx. Right again !!  !! Now I am trying to create a subsummary report which on script execution identifies all records from this month. The goal here is to make the reporting as userfriendly as possible. I have following script and it seems to partly work but does for some reason not display all records compared to the manual imput like from "date...date".

 

Monthly Sales Report Current

Show Custom Dialog [ Title:"This Month Sales Report"; Message: "You can only use this report button until the last day of the current month. In case you exceed this day please use the button "Sales Reports by Day Range" and enter your dates manually. Click OK to continue"; Default Button:“OK”, Commit:“Yes”; Button 2:“Cancel”, Commit:“No” ]

Show All Records

Enter Find Mode [ Specified Find Requests:Find Records; Criteria:T12_INVOICES::Date_Created : “11/*/2014” ] [ Restore ]

Set Field [ T12_INVOICES::Test Date ;  Month ( Get ( CurrentDate ) ) & "/" & Year ( Get ( CurrentDate ) ) ]

Set Error Capture [ On ]

Perform Find [  ]

Go to Layout [ “Subsummary Sales Monthly” (T12_INVOICES) ]

Sort Records [ Keep records in sorted order; Specified Sort Order:T12_INVOICES::Test Date ; ascending T12_INVOICES::Account_Name ; ascending ] [ Restore; No dialog ]

Enter Preview Mode

Show Custom Dialog [ Title:"Please Check Your Report"; Message: "Check your report. If everything is ok, push the "Continue " button right next to where it says  Script Paused  in the right upper corner of the screen. Click OK to continue. Otherwise click CANCEL and GO BACK TO INVOICES"; Default Button:“OK”, Commit:“Yes”; Button 2:“Cancel”, Commit:“No” ]

Pause/Resume Script [ Indefinitely ]

Print [ Records being browsed ; All Pages; Orientation:Portrait; Paper size:8.5" x 11" ] [ Restore: Adobe PDF ]

Unsort Records

Show All Records

Enter Browse Mode

Go to Layout [ original layout

 

Could you tell me if this is correct as is? 

 

Sorry for all the trouble but I guess its somewhat over my head.

[comment]

Show All Records
Enter Find Mode [ Specified Find Requests:Find Records; Criteria:T12_INVOICES::Date_Created : “11/*/2014” ] [ Restore ]
Set Field [ T12_INVOICES::Test Date ;  Month ( Get ( CurrentDate ) ) & "/" & Year ( Get ( CurrentDate ) ) ]

1. No need to Show All Records before a find. A find will replace the current found set anyway.

2. No point in searching the calculated Test Date field; it does not contain any more information (actually, less) that the Date_Created field.

3. To search for records in the current month, independent of the date format in use by the file, do:

Enter Find Mode [ ]
Set Field [ T12_INVOICES::Date_Created; Let ( t = Get (CurrentDate) ; Date ( Month (t) ; 1 ; Year( t) ) & ".." & Date ( Month (t) + 1 ; 0 ; Year (t) ) )

The goal here is to make the reporting as userfriendly as possible.

 

IMHO, presenting the user with a single dialog enabling them to either enter a date range OR select a "current month" option would be a significant improvement towards that goal.

[amerioca]

Tnx a lot for all your support. Works phantastic. And you are right, I could let the user search manually by date or month, which I will have established in the training for the users but I am working with physically with IT illiterates, where very little apart from surfing the net and accessing social platforms, reading/writing html emails is present. So I have created and scripted buttons for monthly sales reports, monthly outstanding invoices, etc all based on the invoice platform.

 

Still having some problems with the charts though as I have a monthly sales chart, the annual sales by month and annual overall sales. But still working on them and will post it here once I get to my limitis.

 

Tnx once again

[amerioca]

I am currently testing the refresh window/object script. I tried refresh object as well as refresh window in order to automatically change the status and current date fields. But for some reason nothing happends. the date stays the same as well as the status. Is there anything I am doing wrong again?

[amerioca]

Forgot to say, I also tried Set Field: Current Date and Set Field: Status and then Refresh Window and also Refresh Object (Current_Date) and Status. No success. Nothing happends.