Calculation for Dues Statement

[Slickwilly9]

I am putting together a calculation which is trying to calculate a cost if the number of sites is in a certain range. The following code is what I have come up with and it does not seem to be working. Eventually once I get this basic function to work I need to add a equation to the output. For example

(385+(Campground Information::# Sites * 1.55) Thanks.

Case (

Campground Information::# Sites = 0 and Campground Information::# Sites <= 25 ; "385" ;

Campground Information::# Sites >= 26 and Campground Information::# Sites <= 50; "400" ;

Campground Information::# Sites >= 51; "405";

"no dues" )

[CobaltSky]

Hello Slickwilly9,

The basic idea is there but you may be complicating things a little more than you need to, and you have some minor errors with the operators.

I suspect you will get the result you need with something along the lines of:

Case (

Campground Information::# Sites < 1; "no dues";

Campground Information::# Sites < 26 ; "385" ; 

Campground Information::# Sites < 51; "400" ;

"405"

 )

[Slickwilly9]

I thank you for the more simplistic code. Although I don't get a result from the calculation when I am creating my solution. And when I un-check the "Do not evaluate if all referenced fields are empty" it displays "no dues" regardless of the value of # sites. But when it is checked nothing displays. Shouldn't it be a simple as just adding the dues field to the solution? Thanks

[CobaltSky]

Yes - that's how simple it is.

In fact I just pasted the calc into a test file here and it worked correctly straight off.

Not sure what you're doing that is different, but I'm attaching a copy of the test file in case you'd like to have a look and compare it to your setup.

TestFile.zip

[Slickwilly9]

That is exactly what I am looking for. Although I am having the calculation done from a different table. I don't know if that is the problem. I have attached my file. It is under dues statement with the reference to Campground Information. Thanks.

CampgroundInformation.zip

[CobaltSky]

Well, Slickwilly9,

I can tell you why you are not seeing a result from the calc in your file.

You have defined the calc in the table called 'Dues Statement'. However there are at present no records in that table.

In order for the calc to return a valid result which corresponds to a given record in the Campground Information table, there will have to be a related record in the table where the calc resides.

If you add a pair of records in the Dues Statement table and enter 'X' and 'Y' respectively into the Campground field (to validate their relationships to your existing X and Y campground records in the Campground Information table), you will see that the calc is indeed working correctly.

[Slickwilly9]

OK. I get that. But in the end result I just want to have a button clicked and have the campground field to be the same as the campground which they were looking at in Campground Information. Is that going to require some sort of script or is that not completely possible with the way I have it setup right now?

[CobaltSky]

Yep - that is possible and you won't need a script.

First go into the Define Database dialog, go to the Relationships tab and double click on the = box in the middle of the line that connects the Campground Information Table Occurrence with the Dues Statement Table Occurrence. In the resulting dialog, on the side that relates to the Dues Statement Table Occurrence, activate the checkbox option which is labelled 'Allow creation of records in this table via this relationship.

Then set up your button on the Dues Statement layout (which is based on the Campground Information table occurrence) and define it to perform the following command:

Set Field [Dues Statement::Campground; Campground Information::Campground]

Clicking on the button will result in the automatic creation of a corresponding record in the Dues Statement table.

[Slickwilly9]

I was able to do all of your instructions, except create

Set Field[Dues Statement::Campground; Campground Information::Campground]

I was only able to create

Set Field[Dues Statement::Campground]

or

Set Field[Campground Information::Campground]

[CobaltSky]

The Set Field [ ] command, whether within a script or attached directly to a button, accepts (and requires) two parameters.

In the 'Specify Button...' dialog, when you select the Set Field[ ] command in the list at the left, you will see *two* buttons labelled 'Specify...' appear in the top right panel of the dialog - one button for each of the two required parameters.

The first parameter (set via the topmost 'Specify...' button) defines the target field - the field that is to be set. The second parameter (set via the lower 'Specify...' button) determines the value that the target field will be set to.

In this instance you will need to specify the target field as:

Dues Statement::Campground

and the result calculation as:

Campground Information::Campground

When you do, the summary of the command on the action line at the top of the dialog will read:

Set Field [Dues Statement::Campground; Campground Information::Campground]

From your last post, it seems that you are setting only one of the two parameters, though it is not clear which one.

[Slickwilly9]

got it

[Slickwilly9]

I have got it all working correctly now. Thanks.

Just looking to tweak it a little. Is there any way to reference a Value List in the calculation so all that needs to be changed is the Value List and not the calculation.

[CobaltSky]

Is there any way to reference a Value List in the calculation so all that needs to be changed is the Value List and not the calculation.

Yes, there is.

Your calc can retrieve the value list contents using the ValueListItems( ) function, and the contents can be parsed out and referenced throughout the rest of the syntax. Or you could store the reference data in a field - perhaps in a related reference table.

However, either way, you haven't indicated whether it is the range breaks (25, 50) that you wish to supply via an external list, or the result values (no dues, 385, 400) - or perhaps both.

By way of example, however, if it were the latter and you had set up a value list called YourList with the values:

no dues

385

400

405

You would be able to set up the calc to utilize those values with a formula along the lines of:

Let(Vn = ValueListItems(Get(FileName); "YourList");

Case (

Campground Information::# Sites < 1; LeftWords(MiddleValues(Vn; 1; 1); 9);

Campground Information::# Sites < 26 ; LeftWords(MiddleValues(Vn; 2; 1); 9);

Campground Information::# Sites < 51; LeftWords(MiddleValues(Vn; 3; 1); 9);

LeftWords(MiddleValues(Vn; 4; 1); 9)

)

Of course, assuming the calc is not stored, changes to the list values will be applied retrospectively unless you set up a historical reference table that applies differential value sets within defined date ranges. But maybe I am anticipating a problem you don't have...

[Slickwilly9]

I have created the button in regards to setting the field. But when I started to import data I realized that the button was not the best solution. I realized that that calculation needs to be done automatically for each record. I took a stab at making a script for it. But I couldn't get it to work. Any suggestions?

[CobaltSky]

Hi Slickwilly9,

Well, since you have opted for a structure in which the calculation sits in a different table from the one the layout you want to review the results from is based on, you will need to do more than simply import data into one of the tables. You need corresponding records in both tables to make the calcs work.

One way to ensure that the related records are created would be to script the import and add a sequence after the Import Records step which loops through the found set (which will contain the imported records) applying a Set Field[ ] to create the related record for each. The sequence woudl look something like this:

Enter Browse Mode[ ]

Go to Layout [Layout your data table is based on]

Import Records[ --your import details here-- ]

If[Get(LastError) = 0]

Go to Record/Request/Page [First]

Loop

Set Field [Dues Statement::Campground; Campground Information::Campground]

Go to Record/Request/Page [Next; Exit after last]

End Loop

There are a few other methods, such as an import of data between the tables (after the first import has run) but the above method is perhaps as good a place as any to start.

[Slickwilly9]

It is telling me that Get(LastError = 0) is an invalid get function. Although it allows it if there is no "= 0". But then what happens is it only performs on the first 8 or 9 records.

[Ender]

Try:

Get(LastError) = 0