Dynamic page Connect 3 Databases

Ask the community
, ,
1

Hello,

I am trying to connect 3 Databases:

Background:

We are a laboratory and our blood test menu is increasing. which will include over 1000 Tests with over 50 Clients.

So far I have been able to connect two databases which has been working, but now i need to separate “Client Information” (currently holds price info) from the prices otherwise the database will become extremely cluttered as we expand.

Essentially, i am trying to connect 3 databases so that it grabs the correct price for the corresponding client and the test being displayed on the dynamic page.

Database Info., Code, and Images are shown below:

Databases:

  1. Test Information (“CompleteMenu1”)
    a. Field Key:
    i. “Test Code_id”
    A. Test code above should match the test code in “Pricing”

  2. Client Information (“Client”)
    a. Field Key:
    i. “email_id”
    A. when user logs in the email should grab the clients information based on their email address

  3. Client Specific Prices (“Pricing”) - This is a new database that i am creating that i need to connect.
    a. Field Key:
    i. “Client Price 1”
    A. Client price that should result out should match the corresponding clients price.
    ii. “testcode”
    A. Should look for a match in “CompleteMenu1”

CODE:

import wixLocation from 'wix-location';
import wixUsers from 'wix-users';
import wixData from 'wix-data';

$w.onReady(function () {
	$w("#CompleteMenu1").onReady(() => {
		let user = wixUsers.currentUser;
		let isLoggedIn = user.loggedIn;
		let userRole = user.role;
		if (userRole === "Member") {
			//$w("#Box1").hide();
			 user.getEmail()
				.then((email) => {
					let userEmail = email;
					let thistestCode = $w("#CompleteMenu1").getCurrentItem().title_id;
				return 	wixData.query('CLIENT')
						.eq("Email_id", userEmail)
						.eq("Test Code_id", thistestCode)
						.descending("_createdDate")
						.find();
									})

			.then((results) => {
					if (results.totalCount) {
						let items = results.items;
						let firstItem = items[0];
						$w("#txtPrice").text = firstItem.Price_id;
						$w("#txtRemarks").text = "Pricing subject to change without notice unless a signed contract is in place.";
					} else {
						
						$w("#btnLogon").show();
						$w("#txtPrice").text = "Sorry, no pricing available, please contact us";
					}

				})
				.catch((err) => {
					console.log(err);
				});
		}
	});
});

$w.onReady(() => {
	if (wixUsers.currentUser.loggedIn) {
		$w("#btnLogon").label = "Logout";
		$w("#profileButton").show();
	} else {
		$w("#btnLogon").label = "Login";
		$w("#profileButton").hide();
	}
});

export function btnLogon_click(event) {

	if (wixUsers.currentUser.loggedIn) {

		wixUsers.logout()
			.then(() => {
	
				$w("#btnLogon").label = "Login";
				$w("#profileButton").hide();
			});
	}

	else {
		let userId;
		let userEmail;

		wixUsers.promptLogin({
				"mode": "login"
			})
			.then((user) => {
				userId = user.id;
				return user.getEmail();
			})
			.then((email) => {
		
				userEmail = email;
				return wixData.query("Members")
					.eq("_id", userId)
					.find();
			})
			.then((results) => {

				if (results.items.length === 0) {

					const toInsert = {
						"_id": userId,
						"email": userEmail
					};

					wixData.insert("Members", toInsert)
						.catch((err) => {
							console.log(err);
						});
				}

				$w("#btnLogon").label = "Logout";
				$w("#profileButton").show();
			})
			.catch((err) => {
				console.log(err);
			});
	}
}

export function profileButton_click(event) {
	wixLocation.to(`/Members/${wixUsers.currentUser.id}`);
}

Images:

“CompleteMenu1”:

825×238 6.23 KB

“Client”:

“Pricing”:


Dynamic Page Example:

1046×564 52.9 KB

The information on the left is connected with the data. We just need the right side bubble to update properly.

Summary:

The system needs to recognize the logged in email from “Client” database. Once the user views a test such as Test Code 8017 like the image/example above shows from “CompleteMenu” the system now needs to find the price under “Pricing” database that matches that client viewing it.
in this example it should know that “Client1” (“pricing” database) and email@example.com (“client” database) is logged and is viewing that specific test code (8017) for it to result out a price of “50” as seen in the pricing database.

Thank you in advance!

2 Likes