Database Cost/Quote Generator

Ask the community
, , ,
1

I am trying to create a quote page on one page that retrieves data from a Dataset, and uses data within the page fields, to create a Cost.

ie. 4 fields on the Page: Start Date [Date], No. of Nights [Text Dropdown], Number of Adults [Number], Number of Children [Number].

In the Database there are the following fields: Title (KeyField), Start Date [Date], End Date [Date], Nights [Text], Adult Price [Number], Child Price [Number], BerthMax6 [Number], BerthMax8 [Number]

From the Setup Fields, I have added a button that calls the right record from the database to show the Child Price and Adult Price based on the Date in the form falling between the Start Date and End Date in the Database, as well as matching the Nights in the form to the Database.
I want to be able to calculate a price rather than display the 2 prices. So the calculation should be…

(Adult Price (from Dataset) x Number of Adults (from Form) ) + (Child Price (from Dataset) x Number of Children (From Form))

This should start me off, but I also want to be able to do a further calculation at some point.
If there are 6 or Less Total People Booking (No of Adults + No of Children) then the Max6Berth Price should be used if the total price is more than this value, if there are 7 or 8 Total People Booking, then the Max8Berth should be used if the Total Price is more than this value.

Any help with this would be great! I can find out details about using Hooks, but I don’t think they work for this because there is data in the Dataset as well as the Input Forms.

Sorry, I am new to most of this, if this is already covered somewhere, please could you add a link, I have searched and just can’t find something that covers this.

Many Thanks
Dave

2

[@David Hunt] Hey Dave! Can you post a link to the page you are developing? Cheers
Steve

3

Thanks Steve,
ASafeHaven.co.uk

4

It is the ‘Pass Prices’ page
https://www.asafehaven.co.uk/copy-of-confirm-my-booking-1

5

@david_hunt13 This is fairly straight forward.
In the response (res) you get from your query:

wixData.query('Passes') 
 // Query the collection for any items whose "Name" field contains  
 // the value the user entered in the input element
  .le('startDate', $w('#StartDate').value)
  .gt('endDate', $w('#StartDate').value)
  .eq('nights', $w('#Nights').value)
    .find()  // Run the query
  .then(res => {

You need to access the first item you receive, presumably there will only be one. If you get more than one result res.length will be > 1 and you can test for that and make a decision what you want to display.

The resulting prices are Numbers so you can use them directly

let adultPrice = res.items[0].adultPrice; 
let childPrice = res.items[0].childPrice;

Once you have the prices you need to get the values from your child and adult counters #Children and #Adults.

let adultCount = $w('#Adults').value;
let childCount = $w('#Children').value;

Then just do the maths:

let totalPrice = (adultPrice*adultCount) +  (childPrice*childCount);

Cheers

6

@stevendc Brilliant. Thanks Steve. I did think it would be straight forward, but just couldn’t seem to find the right way of calling the fields and calculating.
I will give it a go later on! Thanks!

7

Thanks for your help so far @stevendc
I just can’t seem to get the values from the Input Fields to feed through into the results??
I’ve put the latest code below, but the AdultCount and ChildCount don’t populate.
I’ve tried it a few different ways and just can’t get my head around it. Apologies if this is the real basics, I am new to the coding side and self/Google taught so far

import wixData from ‘wix-data’;
import wixLocation from ‘wix-location’;

$w.onReady(function () {

$w("#PriceDetails").columns = [ 
  { 
    "id": "AdultP",       // ID of the column for code purposes 
    // The field key in the collection whose data this column displays   
    "dataPath": "adultPrice", 
    "label": "Adult Price", // The column header 
    "width": 100,       // Column width 
    "visible": true,    // Column visibility 
    "type": "Number",   // Data type for the column 
    // Path for the column if it contains a link  
    "linkPath": "" 
  }, 
  { 
    "id": "ChildP", 
    "dataPath": "childPrice", 
    "label": "Child Price", 
    "width": 100, 
    "visible": true, 
    "type": "Number", 
    "linkPath": "link-field-or-property" 
  }, 
        { 
    "id": "AdultC", 
    "dataPath": "adultCount", 
    "label": "Adult Count", 
    "width": 100, 
    "visible": true, 
    "type": "Number", 
    "linkPath": "" 
  }, 
        { 
    "id": "ChildC",   
    "dataPath": "childCount", 
    "label": "Child Count", 
    "width": 100, 
    "visible": true, 
    "type": "Number", 
    "linkPath": "" 
  }//, 
];

});

export function Search_click(event, $w) {
wixData.query(‘Passes’)
.le(‘startDate’, $w(‘#StartDate’).value)
.gt(‘endDate’, $w(‘#StartDate’).value)
.eq(‘nights’, $w(‘#Nights’).value)
.find() // Run the query
.then(res => {
// Set the table data to be the results of the query
$w(‘#PriceDetails’).rows = res.items;
let adultPrice = res.items[0].adultPrice;
let childPrice = res.items[0].childPrice;
let adultCount = $w(‘#Adults’).value;
let childCount = $w(‘#Children’).value;
$w(‘#PriceDetails’).show();
//$w(‘#BookingResults’).link = “//Bookings/CustomerConfirmation/”& {Booking Ref} ;
//$w(“#ResultBookRef”).value = res.items[1];
//let ResultBookRef = res.items[1];
});
}

8

@david_hunt13 I have annotated your code below. One thing you should also get used to doing is adding console.log() statements that will show up at the bottom of your page in Preview mode.

OK so now it seems you want to update the table you have defined to contain the adultCount and childCount. What you need to do is override the object values you use to give the table data. In this case you are using res.items … $w(’ #PriceDetails ').rows = res.items;

Since you should only be getting one row from your query (multiple rows will need different code). So you start your code with an assignment to $w(" #PriceDetails ").columns. This is being given an array (this is defined using the ‘’ notation) of objects (each of which is defined using the ‘{}’ notation containing key:value pairs).

The important thing about these column definitions is the field key references:
“dataPath”: “adultPrice”
“dataPath”: “childPrice”
“dataPath”: “adultCount”
“dataPath”: “childCount”

In the res record you get back from your Passes data collection you are retrieving a set of objects in an array called items . Each object in your items array will contain a key value pair for adultPrice and childPrice BUT not for adultCount and childCount. You have to add these.

So before you assign res.items to the rows property you need to add the count information.

export function Search_click(event, $w) {
wixData.query('Passes') 
 .le('startDate', $w('#StartDate').value)
 .gt('endDate', $w('#StartDate').value)
 .eq('nights', $w('#Nights').value)
   .find()  // Run the query
 .then(res => {
  /********************/
  // Check how many records are returned and only do work
  // If we get what we expect
  /********************/
   if (res.length === 1) {
      /********************/
      // We have a record to process show it in the log
      /********************/
      console.log("res is: "+JSON.stringify(res));
      
      // Set the table data to be the results of the query   
      let adultPrice = res.items[0].adultPrice; 
      let childPrice = res.items[0].childPrice;
      /*********************/
      // Make sure you are using Numbers for your math
      // If you haven't specifically set the input field type to 
      // Number you will get a String which is not math friendly
      // :-)
      /*********************/
      let adultCount = Number($w('#Adults').value);
      let childCount = Number($w('#Children').value);
      
      /*********************/
      // Assign the adult and child count values
      /*********************/
      res.items[0].adultCount = adultCount;
      res.items[0].childCount = childCount;
      
      /*********************/
      // Now you can add the row information!
      /*********************/
      $w('#PriceDetails').rows = res.items;
      
      $w('#PriceDetails').show(); 
      
      /**********************/
      // Not sure what you are trying to do below if you have
      // Configured a Dynamic Page then you will have a special
      // Dynamic Page column in your Bookings Data collection.
      // If the format of this link doesn't match the appropriate
      // column then your link will not work.
      // I don't see where you are creating the Customer
      // COnfirmation Value for this to work?
      /**********************/
      //$w('#BookingResults').link = "//Bookings/CustomerConfirmation/"& {Booking Ref} ; 
      
      //$w("#ResultBookRef").value = res.items[1];
      //let ResultBookRef = res.items[1];
    } else {
     /*****************************/
     // We didn't get what was expected from the Data Collection!
     /*****************************/
      console.log("Unexpected number of records retrieved from data collection - "+String(res.length));
    }
  }); 
}

Hope this is what you were looking for.

Cheers

9

@stevendc Thank you!!! Works brilliantly! Just as I want it, and now i’ve got the basics, i’m going to have a play.
I’ve managed to get the total cost in :slight_smile: I’m going to work on using a Maximum Value based on another criteria field. So i’ll see how I go with that.
Thinking on that, i’d need to do an If Statement on this, or maybe a Min. ie. I basically want a price checked against the totalCost and display the lowest value. any tips on that would be great,

Is there a way to display the data in currency format? I actually want £0.00, but can’t seem to find how to do it :S

Thanks again for your help on this!
Dave

10

@david_hunt13 Can this be applied to social posts or videos?

11

Hi David

My suggestion at this point is research MDN javascript and all of the articles and examples we have provided on the http://wix.com/code/home site

Also take a look for video blogs on YouTube.

This forum is intended to help people with a knowledge of coding use wix code. The essentials of javascript and text formatting etc. Are covered in many places so start there. Then bring the code you have tried here if you can’t get something to work.

Cheers

12

You’re right Steve thanks for your support and thanks for pointing me in the right direction. I will do that.
Thanks again
Dave

13

No Problem David - we need a great community around Wix Code. You are part of that. With the essentials under your belt you will be able to do some great things and help other peers here. Come back soon!

Cheers

14

[@James Rondon Jr] Hi James I’m not clear on your question. Can you help by creating a new post (feel free to reference this one) with a more specific question that you need help with? Please share any code you have and the site page url.

Cheers
Steve

15

Thanks again. I’m getting there, and now have everything I wanted up and running :slight_smile: