How do I pull information from multiple tables onto the same grid.

Question

I am new to the lightswitch environment. I have created a trucking dispatch system that allows the user to take pickups, select the shipper, consignee information pieces and weight for the shipment.

So a shipment table consists of:

ShipmentTable consists of: ID, PickupDate, Shipper - >CustomerTable LookupTable, Pieces, Weight, Consignee -> CustomerTable, and other various fields

 

CustomerTable consists of: ID, CustomerName, Address, City, State, Zip

Everything works perfect with the relationships. But there is a third table that stores the zipcodes grouped by an area zone that I need to have the area zone for each shipper zip codes looked up and displayed after the shipper zip in the shipment grid. I would think this would be done in the query code like a right join command. I hope this makes sense. I've made it work with the selected item command but is post the same zone on all the shipments on the grid depending which row is selected. Can anyone help?

Zipcode area zone table consists of: ID, Zipcode, Area (Contains: Orange County, Riverside County, etc...)

Answer 1

I'm not exactly sure that I understand why what you want isn't working. So I'll explain two scenarios that I can think of (though I tend to think it's the first one).

Scenario One:

If your grid is based on an entity (Customer), that has a navigation property (ZipCode) & you need to display Customers.SelectedItem.ZipCode.AreaCode, you can use the "Add Other Data" in the screen control tree. Once you see it you kind of know what it's for, but it's not obvious until you know.

You can walk as far down the properties as you need. So Entity.EntityProperty.PropertyProperty.PropertyProperty.Property. That's a bit "contrived", so I hope you get what I mean.

Scenario Two:

If there's a 1-Many relationship relationship between two tables, the screen wizard will offer you checkboxes to tick if you want to include related data. If you don't tick them in the wizard, you can still add them to a screen, becuase on the left-hand side, under the screen property you'll see a + for each available related table. You can then drag the newly added property to the screen control tree.

---

Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands

If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer"

By doing this you'll help people find answers faster.

Answer 2

I tried both methods to bind the shipper zipcode to the zone table together so I can drag the zone from the zone table that holds the area zone associated to the postal code and it would be displayed on each line of the grid according to zipcode.

I tried creating a search page with the shipments so each item is displayed on the grid and query of the postal code on the zone areas. But how do I bind the postal code from the shipper zipcode to the zone query so the string for each zone is displayed on the grid for each shipment entry. I've been fighting this for a week. And tried all sorts of configurations. I really appreciate all the help.

At one point I set zone areas postal code to: SearchPendingPickups.SelectedItem.Shipper.PostalCode. And the zone is displayed as each shipment is selected but it shows all the shipments are on that zone as the shipments are selected according to zipcode. I need it to work the other way around. As you can tell I'm confused on how to bind the tables properly.

See attached pictures. Maybe it will help.

---

Microsoft Forums.

Answer 3

I showed you how to add the Zone to the screen. You can't "drag the zone from the zone table", you have to use "Add Other Screen Data", liked I showed you in the image.

Is there a relationship between the ZipCode table & the Zone table?

---

Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands

If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer"

By doing this you'll help people find answers faster.

Answer 4

There is no relationship setup on the customer table to the zone table. I wasn't sure how to tell the relationship to use the postal code of the customer to the area zone postal code to make a link. Or does the relationship get created from the shipments table where the customers lookup table exists?

I can't make the zipcode from the shipper on the shipments table set the query in the zone table to pull each zone as the grid is populated then use the other screen data option to put the zone in the grid? I guees I don't understand where to set the relationship for the zone table that has all the zip codes loaded with the zones. And bind them to the shipments or the customer table. I thought it would be like creating a look up table to find the zone for that zipcode.

---

Microsoft Forums.

Answer 5

Here is what the table looks like to search for the approprate zone to be displayed on the grid.

---

Microsoft Forums.

Answer 6

Added a relationship and used the Other screen Data to add the connection. Still no data...

---

Microsoft Forums.

Answer 7

I would have thought that there'd be a realtionship between the ZipCode table & the Zone table. Each ZipCode belongs to a specific Zone, doesn't it?

If you don't have a relationship defined between the two tables, LightSwitch has no idea how to display what Zone for any ZipCode.

---

Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands

If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer"

By doing this you'll help people find answers faster.

Answer 8

I had to do a work around on this problem. My customer needed this working today so I created a field to store the zone and I push the zone area on to the shipment when creating or saving a change on the shipment. The good thing is now that it is not a lookup table. I can now sort by this field.

Thanks for the guidance. I will play with this on the weekend to see what I'm doing wrong in my relationships.

---

Microsoft Forums.