Making an order app. Having some issues.

Making an order app. Having some issues.

This is my first time with zoho so i am not very skilled at this. I am stuck and need help finishing my app. First let me explain what i am trying to make then I will explain where i am stuck.

I am making an order app. Basically all i need is a app that adds products to an form and then make some designed forms after. I am trying to stop myself from creating two separate excel spreadsheets with the same info. I have attached the sheets. So you can see what I want to produce.

  1. So I have a vendor form so that i can store all the vendor info and i can select the vendor and attach all the info to my order easily. It has the following fields
    1. Vendor Name
    2. Vendor Address
    3. Phone Number
    4. Fax Number
    5. Vendor Contact
    6. Vendor Email
  2. I also have a items form so that i dont have to re-enter each item every time i do an order. It has the following fields.
      1. Vendor (lookup field from Vendor form)
      2. Item description (Name)
      3. Vendor Item Number
      4. My Companies' Item Number
      5. Case Qty
      6. Case Price
      7. Unit Price

Now i have an order form and this is where i am stuck. I have a lookup field to select the vendor but how do i pull the other vendor info into the form. In this form i also have a sub form with a lookup field to select multiple items but i had an idea to filter the items by vendor. However i asked support they said you can't filter based on a field in the main form. So my question is maybe my design is wrong. Maybe there is a better way to do what i want. maybe i need more forms? or maybe less? I am sure there is a way to get what i want done. Maybe someone here can help me maybe start over and get the right outcome.