Info. from 3 tables to be displayed inlist form

Hi,

I have a situation, where i need to display the information from 3 tables,

Table-1 contains master values

Table-2 contains transaction values.

Table-3 contains transaction values.

The list form will be just like a Dash-Board type of wherein all the values will be displayed.

And i cant use the matrix form, bcoz the values must be displayed in list wise.

Please suggest

Thanks

Vikas

Not enough information to give afull answer, but to start with, at first impresion it looks like creating a form based on integer, and then populating it with the data fromt he appropriate tables is the way to go.

Something like this…

This is based on just two tables, (Item and BOM Component), but can just as easily be applied to three tables.

Hi,

Let me clear with the example, this is for a poultary farm,

Table-1 is a master table which contains standard values like, Age, Std. Weight, Std. Height, Std. Intake etc. Here data will be stored based upon the age, like on day 2 (Age=2), wht is the std. weight & feed intake, etc

Table-2 is a transaction table, which contains daily basis entry of Age, weight, height, intake for poultary.

Now the 3rd table is another transaction table, which captures the no. of mortality of the chicks.

So for management perpose, i have to create a list form wherein, it contains all the master data, and corresponding transaction details.

like below format,

Age | Mortality | Mortality % | Std. Feed Intake | Actual Intake | Std Weight | Actual Weight |

I hope i have have cleared this

I didn’t get that.

Do you need a three level form - i.e.

Sales Header

  • Sales Line
  • Item Tracking Lines

Or do you need two different childs from the same parent?

Customer

  • Orders
  • Posted Invoices

To me this looks like a standard FlowField situation.

So all you need is some flowfields in table 1 (your base table) summing up the values in tables 2 and 3.

Its not parent or child data…its simple list form where we have to populate the data from 3 different tables,

See, I am developoing this form for poultary farm, where the daily basis entry will be made like feed Intake, weight of the chicks, mortality etc.

I have a master table which contains Age (PK), Std. Intake, Std. Weight…

in list form, against the age field i have to display the actual intake tht was given to chicks, Actual weight of chicks, and the no. of mortality.

Example:

On Day 5 (i.e Age = 5), the Std. Feed Intake = 23gms, the Std. Weight = 108. against this i have to display the actual feed intake, actaul weight etc.

format:

Age | Std. Feed Intake | Actaul Intake | Std. Weight | Actaul weight | Mortality

5 | 23 | 22 | 108 | 106 | 1

  1. Age comes from Master Table

  2. Std. Feed Intake & Std. Weight comes from Table 1—its a transaction table like journal

  3. Mortality comes from another transaction table

Common fields in these tables is Age and Farm No.

Do you want to insert Data in this Form?

If not, then I would do as Thomas suggests, and create Flow fields in your master table (if you need an example on how to do this, have a look at Table 27, fields 71…80).

Another question, do you really need the mortality (3rd) table? To me it looks like you could put that information also in the 2nd Table because the key values look to be the same. This would also make it easier if you want to insert data in this Form.

Simple Just use 3 Forms Main and Sub Form and Sub - SubForm.

How do you do 3 tables?

Main Form Table 1 and SubForm Box (Customers List)

Sub Form Table 2 and SubForm Box (“Sales Header” List Linked to Customer by “Sell-to customer No.”)

Sub - Sub Form Table 3 (Sales Line List Linked to “Sales Header” by “Document Type” and “Document No.”)

As you move through the Customers the orders and lines will change

Is there a date involved ?

I mean you are feeding your chicks 7 days a week. In the transaction table you would summarize those entries.

I don’t think that it would help that you see the total of the feedings per age. There must be some kind of date filter involved, so you do at least know three values for your flow fields.

Age, Farm and Date.
This should help to define the flowfields properly.

Vikas,

you are looking at this issue too technically. FOr that reason its very hard for everyone to discover exactly what you want. Though form my interpretation of what you are after, Thamas has suggested the correct solution, and you should concentate on that path.

But take a step back. In reality what you are doing here is solving a business issue with coding. It lloks like a simple problem complicated by over reaching solutions. So instead of try to to ask yourself (and this forum) “how do I program this”, take it from a differnt starting point, and say “what am I trying to achive”.

I.E. address the issue of WHAT you want to achieve, not HOW you want to achive it. Once you know what you need to have on the screen, put it in an excel spread sheet, and then put it here. Like this

Age Std. Feed Intake Actaul Intake Std. Weight Actaul weight Mortality
5 23 22 108 106 1
5 22 21 110 98 1
3 21 22 90 120 0

Then explain where each Column’s data is sourced from, and what its relation ship is the trhe rest of the data. The problem is that at the moment there are too many interpretations of what you are trying to achive.

Try to solve the business issue, NOT the coding issue.

PS Community Server really looks soooo much nicer than Snitz. Being able to copy paste straight from Excel is much nicer than attachments and all.

I agree - it’s hard to come up with an answer, if you are not sure where the goal is.


Yes - I love it, I love it, I loooooooove it! [ip]

Here is a form we use, it is a master form list at the top, with multi-ple sub form list below, all relate to the current line you are on it the master list at the top. This seems like what you are asking for.

http://www.bayshoretruck.com/truck/multitableform.jpg

David, How did you post the screen shoot, I could only figure out how to post a link the picture on my website, would rather not have to leave the picture there forever so the link remains valid.

Its hosted on my site, and yes that is a sort of issue, but later I will just link to it as a down load. I t just happened that the file was ther eon my site, so it was quick to do it that way.

As to the issue, I think that Thomas worked out that its not a sub form scenario, but a flow field situation. basically there is an issue of clearly defining the issue, so I think the jury is still out to know how to solve it. [;)][:$]

Hmmm read it again, the information could be contained in a flowfield for a sum total displayed as a single value, but the poster wants to show three tables in one form, one is a Header record, the second and third are historic transaction tables, which are many to one, so if we look at standard tables, we might want, Customer, Posted Invoices and Posted Credit Memos on one form, or a lot of developers have an item dashboard, with variants, costs, prices and uom’s so is it a subform or flowfield solution?

Is this topic finished?

Cheers

Tony

No, I think at this point we are waiting for information from Vikas as to exactly what he is trying to achieve.

the issue is closed now. sorry for not informing u.

i mean i solved it by using “setrange and setfilters, flow fields”

the issue was…customer wants a DashBoard like form…wherein he can view all the records of chicks.

records come from 3 tables

1 - Mater table → fields like Age,Date, and some standard values.

2- Transaction table - fields-> Age, Date and some fields,

3-another transaction table - fields ->Age, Date and some fields

So one tabular form wherein i need to club all the values based upon age and date.

so i did it. and i thank each one of u…thanks very much

Thanks & Regards,

Vikas

Vikas, thanks for giving feedback, it’s always appreciated. [:D]

And if I could ask you one more favour. When you get a resolution to a question, please give the post a star rating, which shows how good the solution was and also mark the topic as RESOLVED (you can find thread status at the top of the thread as per the attached picture), so other users know that the thread contains a solution.

Thanks.