Hey,
I have managed to sucsefsuly set up a coupon system fro payment on my website. The one area that I’m not sure about is how to query the expiry date in the database.
Just need a little help what query to use, the field key is expiryDate
This code is the working code without the added query.
export function applyCoupon_click(event) {
wixData.query("DiscountCoupons")
.eq('discountCode', $w("#coupon").value)
.contains('category', $w('#courseCategory').text)
.find()
.then( (results) => {
decideDiscount(results);
});
}
Many thanks in advance!
Hey
Let’s say you have a field in your data collection with your expiry date called expirationDate. You want to make sure that you query coupons that have not expired yet.
export function applyCoupon_click(event) {
wixData.query("DiscountCoupons")
.eq('discountCode', $w("#coupon").value)
.contains('category', $w('#courseCategory').text)
.lt('expirationDate', new Date()) // Will get records less than today
.find()
.then( (results) => {
decideDiscount(results); // What do you do here?
});
}
Thank you @andreas-kviby that’s what I was looking for! The full code for anyone else if it helps 
export function applyCoupon_click(event) {
wixData.query("DiscountCoupons")
.eq('discountCode', $w("#coupon").value)
.contains('category', $w('#courseCategory').text)
.lt('expiryDate', new Date())
.find()
.then( (results) => {
decideDiscount(results);
});
}
function decideDiscount(results) {
try {
let Item = results.items[0];
if (Item && Item.discountCode === $w("#coupon").value) {
$w("#discount").text = String(Item.value);
$w("#discountbox").text = String(Item.value) + "% DISCOUNT APPLIED";
getDis();
$w("#invalidCode").hide();
} else {
$w("#invalidCode").show();
}
} catch (error) {
console.log(`Error occurred: ${error}`)
}
}
async function getDis() {
let valueAfterDiscount = (Number($w("#amount").value)) - ((Number($w("#amount").value/100)) * (Number($w('#discount').text)));
await $w("#discountbox").show();
$w("#applyCoupon").disable();
$w('#amount').value = parseFloat(String(valueAfterDiscount)).toFixed(2);
$w('#textDeposit').value = "£" + parseFloat(String(valueAfterDiscount)).toFixed(2);
$w('#deposit').value = 1
}
Hey @andreas-kviby , Since returning from a trip I have tested the above code and it doesn’t seem to work… I didn’t get a chance to test before I left for the trip.
If I remove the:
.lt('expiryDate', new Date())
then it all works, but as soon as put this line of code back in it always displays the “invalid code” text, any ideas?
@stephenmccall Is your expiryDate field of type DateTime?
@andreas-kviby it is indeed.
@stephenmccall You need to clear out time
let date = new Date();
date.setHours(0,0,0,0); // clear out the time, just leave date
@andreas-kviby I had thought it might be something to do with the format.
I have put the code in but have the same issue, it stops any of the codes working… I can just manually remove the coupon on the expiry date if need be.
@stephenmccall Give me a sample value from datetime field of one coupon
@andreas-kviby I have got the date working now. I ended up using .gt and this seemed to solve the issue. I do have another slight issue that you might be able to help with…
The problem now is that I don’t want the coupons to apply to every course, so have set parameters to filter through query. The problem is I have a variety of courses within the Category field key that i want to be included in the promotion but it seems to apply the coupon to every course, I’m using .contains and I’m sure that because ‘Winter’ is included in the field then it applies it to every winter course ( These are the courses that I want to limit).
I changed the query .eq but it doesn’t work on any courses after that because there are so many entries in the same field
I suppose the simple question is, does a comma separate individual entries?
I also tried to inc .not then set up another field in the database to reference the course that I don’t want to be included but it still let the coupon code work on all courses.
Thank you!
Thank you
@stephenmccall Hey
Do you use a text field with comma separated values or a reference field?
@andreas-kviby Thank you for getting back to me,
This is the line that’s causing me issues now.
.contains('category', $w('#courseCategory').text)
The ‘property name’ is the field key from the database in the picture above, which show the courses that I would like to be included in the promotion. As you can see they are separated by commas in the field, I’m not sure if this actually separates anything?
The string is checked from a text box on the screen to reference against the field key i.e. canoe holidays or sea kayak holidays or hillwalking etc.
Would I need to have separate fields for each course then set up .or on the query? It would just make the code pretty long if this is the case.
@stephenmccall Hey. Ok so what value do you have in the $w(’ #courseCategory ').text that you will use to compare?
@andreas-kviby Hey,
The value depends on which course category the buyer has selected but could include:
Hillwalking
Scrambling
Rock Climbing
Hiking Hillwalking Holidays
Intro/Improver Canoe
Qualifications
Open Canoe Holidays
Sea Kayak Holidays
Winter Skills
And in this case I don’t want it to include:
Winter Mountaineering
Winter Climbing
So I’m referencing the $w(‘#courseCategory’) against the category filedKey but it doesn’t behave as I would like, I’m expected it was because Winter was in the field when referencing, but I removed this to test and when booking any winter course it still applied the discount… I’m expecting that if any of the letters apear then this will make this valid? I’m not sure exactly how ‘fine’ the .contains query is? letters? whole words?
@stephenmccall Hey
The .contains behaves like the standard SQL like I suppose so it will find anything inside the string. So, when you have the categories in a comma separated field you can’t run contains and then a NOT because they will kill each other because it is one field.
The more advanced version would be if you create a Data Collection called Categories and then entered all categories there in the title field.
Then in your courser you change the category field to a reference filed with multiple items and then when you filter you use contains and it should work even now you can use not equal to because the items is referenced.
If you need any help please visit wixshow.com and book an hour of coding. On Sale this week
@andreas-kviby Thank you for your input, when you mentioned about the referencing, I thought that would be the answer… upon some further investigation, the reference field wouldn’t work even with just one value.
So after scratching my head, I thought there must be some other issue, so I found another area of code that was conflicting the $w(’ #courseCategory ').text value. This element was hidden so I couldn’t see the field when testing. I made this visible and fixed the fault fixed. Then applied some more testing.
The working code that solved the issue
The .contains for the query of the ‘category’ field key
the fieldKey is Text and the courses are separated by commas.
Even for instance if the word ‘Winter Skills’ is in the database field, then only will discount be applied for Winter Skills, but not any other courses with winter in the name i.e. Winter Mountaineering or Winter Climbing.
I’ve tested every course and put them into the database field one by one and testing each time, then tested them all again after completion and it works great! Thank you once again!
I do have another issue that I might just take you up on your offer… Let me try to do some learning and if I get stuck I will drop you an email 
